[TYPO3-commerce] Speeding up commerce?

[Franz Koch]

Hi Ingo,

> Our Shops are running without any problems, and without speed problems.
> The MySQL Problems looks like a cartesian product. Could you please 
> start the slow query log and post your results here?

I already activated the slow query log. Here some stats:

Queries > 2 seconds:
------------------------------
# User at Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 163
SELECT cu_symbol_left, cu_symbol_right, cu_sub_symbol_left, 
cu_sub_symbol_right, cu_decimal_point, cu_thousands_point, 
cu_decimal_digits, cu_sub_divisor
FROM static_currencies
WHERE cu_iso_3="EUR";
------------------------------
after that I added a new index key to the static_currencies including 
cu_iso_3 as key and this 'problem' was gone. Btw - why does moneyLib 
have to fetch the static info over and over again from db? It should use 
a temporary cache for it in some global var.


Queries > 1 second:
------------------------------
# User at Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 81
SELECT uid
FROM pages
WHERE doktype=2 and module="dam" AND deleted=0;


# User at Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 6275
SELECT uid
FROM tx_commerce_articles
WHERE classname = 'sysdelivery';


# User at Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0  Lock_time: 0  Rows_sent: 0  Rows_examined: 2
SELECT *
FROM sys_lockedrecords
WHERE sys_lockedrecords.userid!=1
	AND sys_lockedrecords.tstamp > 1191584481;

# User at Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0  Lock_time: 0  Rows_sent: 3  Rows_examined: 44
SELECT distinct 
tx_commerce_attributes.uid,tx_commerce_attributes.sys_language_uid,tx_commerce_articles.uid 
as article ,tx_commerce_attributes.title, tx_commerce_attributes.unit, 
tx_commerce_attributes.valueformat, 
tx_commerce_attributes.internal_title,tx_commerce_attributes.icon, 
tx_commerce_articles_article_attributes_mm.sorting

FROM 
tx_commerce_articles,tx_commerce_articles_article_attributes_mm,tx_commerce_attributes

WHERE 
tx_commerce_articles.uid=tx_commerce_articles_article_attributes_mm.uid_local 
AND 
tx_commerce_attributes.uid=tx_commerce_articles_article_attributes_mm.uid_foreign 
  AND tx_commerce_articles.uid_product = 3791  AND 
tx_commerce_articles.uid in (3796) order by 
tx_commerce_articles_article_attributes_mm.sorting;

------------------------------

I already added a new index key to tx_commerce_articles for the field 
'classname', so this is fixed, too. But commerce is still slow (around 5 
to 7 seconds for pages on first hit).

I'm no MySQL expert, but might it help to change the database tables 
from myISAM to InnoDB?

> Could you also give some figures on your installation (amount of 
> products, categories, attributes ...) and installed extensions?

well - the shop is not very special:
- 6272 Products
- 6277 Articles
- 9061 Prices (some only for certain fe_groups)
- 395 Categories (some only visible for certain fe_groups)

used extensions besides commerce (and related):
- dam 1.0.11
   (using references with dam_ttcontent, css_filelinks, dam_filelinks,
    dam_multimedia)
- feuserloginsystem 0.1.2 (modified version)
- sr_feuser_register 2.5.7
- fh_library 0.0.19 (needed for sr_feuser_register)
- moneylib 1.2.1
- graytree 0.1.1
- static_info_tables 2.0.5
- templavoila 1.3.3
- some small own extensions for modifying the BE


Hope that helps so far and thanks for the help.

--
Kind regards,
Franz Koch