Jump to content
xt:Commerce Community Forum

Performance Problem beheben

lingott

By lingott,
in PHP & MysQL Forum

Recommended Posts

lingott Newbie

lingott

Posted
Posted

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_status); 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_id | 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_status); 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

Link to comment
Share on other sites
lingott Newbie

lingott

Posted
  • Author
Posted

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.

Link to comment
Share on other sites
reinhardt Newbie

reinhardt

Posted
Posted

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.

:confused:

Link to comment
Share on other sites
lingott Newbie

lingott

Posted
  • Author
Posted

Ist ja auch Quatsch, der Index

ALTER TABLE `products_to_categories` ADD INDEX ( `products_id`,`categories_id` )  [/PHP]

ist doch schon vorhanden und kann demnach auch nicht nochmal angelegt werden.

Das ist kein Quatsch.

Wenn du dir das Explain richtig angeguckt hättest dann würdest du den Unterschied sehen.

PHPmyadmin sagt zwar, dass ein Index für das Feld bereits existiert jedoch steht das Feld "categories_id" an zweiter Stelle im Primary Key.

Der der Index kann also nur verwendet werden, wenn im WHERE auch das Feld "products_id" abgefragt wird.

Der MySQL Query Optimizer macht in diesem Fall keinen so guten Job und baut die Query nicht ganz optimal auf.

Vor der Änderung ist der Optimizer hingegangen und hat erstmal alle Produkte aus der Tabelle "products" ausgelesen und hat erst danach geschaut welches Produkt ist eigentlich in der gesuchten Kategorie.

Nach meiner Änderung macht er es genau anders herum.

Er guckt erst nach welche Produkte in dieser Kategorie sind und holt sich dann aus der Tabelle "products" alle nötigen Daten.

Schau dir das Explain an und du wirst den Unterschied sehen.

Jetzt nicht verwirren lassen aber man kann den Index auch weglassen wenn man den Query Optimizer umgeht und ein STRAIGHT JOIN einbaut.

Aber das ist eher was für Profis und es gibt auch ein paar Nachteile.

~Andreas

Link to comment
Share on other sites
kurotecc Newbie

kurotecc

Posted
Posted

Hallo

Also ich habe das so probiert:

Ich habe mir den Eintrag products_id to categories_id geöffnet und dan sehe ich zwei Einträge, products_id und categories_id. Ich habe dann auf categories_id das Feld index angeklickt und auf "bearbeiten" geklickt.

Dann stand in dem Fenster

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

und das habe ich geändert in

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

Dann habe ich auf ok gedrückt und dachte nun dass die Änderungen vorhanden sein sollten. Ich habe den Index dann noch mal geöffnet und alles war beim Alten. Ich muss dazu sagen, dass ich kein Profi bin aber schon eine Zeile schreiben kann.

Vielleicht hab ich ja auch was falsch gemacht.

Wenn das so nicht richtig war, kannst du ja noch mal schreiben wie ich genau vorgehen muss.

Gruß

Georg

Link to comment
Share on other sites
lingott Newbie

lingott

Posted
  • Author
Posted

Wenn das so nicht richtig war, kannst du ja noch mal schreiben wie ich genau vorgehen muss.

Am Ende müsstest du folgenden CREATE TABLE haben:

CREATE TABLE `products_to_categories` (

`products_id` int(11) NOT NULL default '0',

`categories_id` int(11) NOT NULL default '0',

PRIMARY KEY (`products_id`,`categories_id`),

KEY `categories_id` (`categories_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Eigentlich keine schwere Sache einen Index anzulegen.

Kannst du auch auf www.mysql.de in den Docs nachlesen.

Link to comment
Share on other sites
kurotecc Newbie

kurotecc

Posted
Posted

Aha???!!???

Sieht echt nicht schwer aus - isses aber für mich. Eilig ist das ja nicht da der Shop ja läuft, aber jede Leistungssteigerung ist natürlich willkommen.

Ich werde das da mal nachlesen und wenn ich keinen Rat mehr weiß, frag ich dich mal ob du mir das nicht mal machen kannst.

Gruß

Link to comment
Share on other sites
sff Newbie

sff

Posted
Posted

Ist ja auch Quatsch, der Index

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

ist doch schon vorhanden und kann demnach auch nicht nochmal angelegt werden.

Stimmt, das gehört genau anders herum. Also entweder man legt, wie lingott meinte einen zusätzlichen Index über categories_id an: 

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

oder man dreht gleich den vorhandenen Primarykey um, dass müsste theoretisch genauso effizient sein.

Link to comment
Share on other sites
lingott Newbie

lingott

Posted
  • Author
Posted

oder man dreht gleich den vorhandenen Primarykey um, dass müsste theoretisch genauso effizient sein.

Das würde ich nicht machen da sonst der Index für "products_id" unter Umständen nicht mehr verwendet wird.

Nämlich genau dann wenn im WHERE nicht nach "categories_id" gefiltert wird.

Link to comment
Share on other sites
bluerate Newbie

bluerate

Posted
Posted

Hallo Zusammen

Eure Diskussion habe ich mit grossem Interesse gelesen! Nach der Überprüfung der products_to_categories-Tabelle habe ich festgestellt, dass bei mir der Primär-Index['products_id,'categories_id] lautet.

So wie ich Euch verstanden habe sollte dieser Index genau umgekehrt lauten, also:['categories_id,'products_id]!!

Habe ich Euch richtig verstanden?

Ich danke Euch für Eure Antwort.

Gruss - bluerate

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...