Performance Problem beheben

[lingott]

Hallo,

ich habe vor kurzem einen Shop mit ca. 140.000 Artikel aufgesetzt.
Insgesamt gab es ca. 250.000 Verknüpfungen in der Tabelle "products_to_categories".
Mysql: 5.0.32

Der Shop war beim Katalog durchklicken total langsam.
Jede Seite dauerte mindestens 10 Sekunden bis diese aufgebaut wurde.
Beim zweiten Aufruf ging es dank dem mysql query cache etwas schneller (5 sekunden).
Doch das war mir immer noch zu langsam.

Ich bin dann über die Tabelle "products_to_categories" gestoßen und mir ist aufgefallen, dass auf dem Feld "categories_id" kein Index gelegt ist.
Zwar gibt es den Primary Key über "products_id" und "categories_id" doch der mysql optimizer verwendet diesen nicht bei der Query Optimierung.

Setzt man direkt einen Index auf "categories_id" so wird die Seite nun in weniger als einer Sekunde geladen.

Vielleicht hilft das dem ein oder anderen weiter der auch das selbe Problem hat.

Es wäre auch nicht verkehrt diesen Index für alle weiteren Xtcommerce Versionen zu setzen.

ZUSATZINFOS:
####################
Ohne Index:
####################
mysql> EXPLAIN SELECT * FROM products p, products_description pd, products_to_categories p2c, categories c WHERE c.categories_status = '1' AND p.products_id = p2c.products_id AND p.products_id = pd.products_id AND p2c.categories_id = c.categories_id AND c.parent_id = '2849' AND p.products_status = '1' AND pd.language_id = '2' GROUP BY pd.products_name ORDER BY p.products_date_added DESC LIMIT 9;
+----+-------------+-------+-------------+----------------------------------------------------+--------------------------------------------+---------+-----------------------------------------------------+--------+-----------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+----------------------------------------------------+--------------------------------------------+---------+-----------------------------------------------------+--------+-----------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | c | index_merge | PRIMARY,idx_categories_parent_id,categories_status | idx_categories_parent_id,categories_status | 4,1 | NULL | 3 | Using intersect(idx_categories_parent_id,categories_stat us); Using where; Using temporary; Using filesort |
| 1 | SIMPLE | p | range | PRIMARY,products_status | products_status | 1 | NULL | 134802 | Using where |
| 1 | SIMPLE | pd | eq_ref | PRIMARY | PRIMARY | 8 | xtcommerce.p.products_id,const | 1 | |
| 1 | SIMPLE | p2c | eq_ref | PRIMARY | PRIMARY | 8 | xtcommerce.p.products_id,xtcommerce.c.categories_i d | 1 | |
+----+-------------+-------+-------------+----------------------------------------------------+--------------------------------------------+---------+-----------------------------------------------------+--------+-----------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)


mysql> reset query cache; SELECT p.products_id FROM products p, products_description pd, products_to_categories p2c, categories c WHERE c.categories_status = '1' AND p.products_id = p2c.products_id AND p.products_id = pd.products_id AND p2c.categories_id = c.categories_id AND c.parent_id = '2849' AND p.products_status = '1' AND pd.language_id = '2' GROUP BY pd.products_name ORDER BY p.products_date_added DESC LIMIT 9;
Query OK, 0 rows affected (0.00 sec)

+-------------+
| products_id |
+-------------+
| 690007 |
| 690057 |
| 690596 |
| 690616 |
| 690636 |
| 690652 |
| 690677 |
| 599805 |
| 602618 |
+-------------+
9 rows in set (9.20 sec)



####################
Mit Index:
####################
mysql> EXPLAIN SELECT * FROM products p, products_description pd, products_to_categories p2c, categories c WHERE c.categories_status = '1' AND p.products_id = p2c.products_id AND p.products_id = pd.products_id AND p2c.categories_id = c.categories_id AND c.parent_id = '2849' AND p.products_status = '1' AND pd.language_id = '2' GROUP BY pd.products_name ORDER BY p.products_date_added DESC LIMIT 9;
+----+-------------+-------+-------------+----------------------------------------------------+--------------------------------------------+---------+--------------------------------+------+-----------------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+----------------------------------------------------+--------------------------------------------+---------+--------------------------------+------+-----------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | c | index_merge | PRIMARY,idx_categories_parent_id,categories_status | idx_categories_parent_id,categories_status | 4,1 | NULL | 3 | Using intersect(idx_categories_parent_id,categories_stat us); Using where; Using temporary; Using filesort |
| 1 | SIMPLE | p2c | ref | PRIMARY,categories_id | categories_id | 4 | xtcommerce.c.categories_id | 314 | |
| 1 | SIMPLE | p | eq_ref | PRIMARY,products_status | PRIMARY | 4 | xtcommerce.p2c.products_id | 1 | Using where |
| 1 | SIMPLE | pd | eq_ref | PRIMARY | PRIMARY | 8 | xtcommerce.p.products_id,const | 1 | Using where |
+----+-------------+-------+-------------+----------------------------------------------------+--------------------------------------------+---------+--------------------------------+------+-----------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)


mysql> reset query cache; SELECT p.products_id FROM products p, products_description pd, products_to_categories p2c, categories c WHERE c.categories_status = '1' AND p.products_id = p2c.products_id AND p.products_id = pd.products_id AND p2c.categories_id = c.categories_id AND c.parent_id = '2849' AND p.products_status = '1' AND pd.language_id = '2' GROUP BY pd.products_name ORDER BY p.products_date_added DESC LIMIT 9;
Query OK, 0 rows affected (0.00 sec)

+-------------+
| products_id |
+-------------+
| 688849 |
| 688243 |
| 687773 |
| 688084 |
| 603126 |
| 685805 |
| 683698 |
| 683599 |
| 682463 |
+-------------+
9 rows in set (0.06 sec)




Gruß,
Andreas

[John Steed]

...öhm, GEIL!

Und Du hast wirklich nur das Datenbankfeld auf "index" gestellt? Oder haste noch irgendwas am Shop geändert? Ich will nur sichergehen, bevor ich's selbst ausprobiere...

[luxuspur]

hallo,
würde mich auch interessieren und wie man was und wo ändern muss.
danke

[sff]

Von 0.0814 sek auf 0.0046 sek bei 5000 Produkten.
Schreib's doch mal in den Bugtracker, damit's nicht verloren geht...

[sff]

Gemäß diesem Beitrag: http://www.xt-commerce.com/forum/sho...6&postcount=19

dauert die Abfrage nach:

PHP-Code:

ALTER TABLE `products_to_categories` ADD INDEX ( `products_id`,`categories_id` ) 


jetzt nur noch 0.0005 sek.

[sff]

Wer misst jetzt mal den Performance-Gewinn der zweiten dort genannten Optimierung?

PHP-Code:

ALTER TABLE `orders_products` ADD INDEX ( `orders_id`,`products_id` ) 


[lingott]

Zitat von John Steed

...öhm, GEIL!

Und Du hast wirklich nur das Datenbankfeld auf "index" gestellt? Oder haste noch irgendwas am Shop geändert? Ich will nur sichergehen, bevor ich's selbst ausprobiere...

Ja. "nur" einen Index gesetzt.

[lingott]

Zitat von sff

Von 0.0814 sek auf 0.0046 sek bei 5000 Produkten.
Schreib's doch mal in den Bugtracker, damit's nicht verloren geht...

Habe ich schon gemacht.
Aber leider habe ich nicht genug Zugriffsrechte um deinen Link zu öffnen.
Kannst du mir den Text auch per Mail zuschicken?
Würde mich interessieren was du gepostet hast.

[kurotecc]

Könnte mir jemand genau sagen was ich da wo ändern muss? Ich habe auch zur Zeit dieses Performance Problem.

Versucht habe ich es, aber die Änderungen werden nicht übenommen. Habe Version 4.1.12.

Danke im Voraus.

[reinhardt]

Wenn ich den Befehl:
ALTER TABLE `products_to_categories` ADD INDEX ( `products_id`,`categories_id` );
ausführe, bekomme ich hinterher in phpMyAdmin folgendes angezeigt:
Die Index-Typen INDEX und PRIMARY sollten nicht gleichzeitig für die Spalte `products_id` gesetzt sein.