✔ Performanceverlust bei Abfrage unter Verwendung von order by

[xtramen01]

Hallo Leute,

habe ein Problem mit einer Abfrage.
Wenn ich die Ergebnisse Sortiere mit order by, dann wird die Abfrage bei ca. 20.000 Artikeln sehr langsam.

Ich kann mir das nicht erklären.
Index ist gesetzt.

Hier mal die Abfrage (mit left join wirds auch nich besser):

EXPLAIN SELECT cd.categories_name, p.section_id, p.products_id, p.products_image, p.products_tax_class_id, p.products_price
FROM products p, products_to_categories p2c, categories_description cd
WHERE p.products_id = p2c.products_id
AND p2c.categories_id = cd.categories_id
AND cd.language_id = '2'
AND p.products_status = '1'
AND (
p.section_id = '0' || p.section_id = '13'
)
ORDER BY p.products_date_added DESC
LIMIT 10

und hier mal das was phpmyadmin ausgibt:

Zeige Datensätze 0 - 9 (10 insgesamt, die Abfrage dauerte 0.0008 sek.)

starting 	0.000057
Opening tables 	0.000265
System lock 	0.000003
Table lock 	0.000011
init 	0.000023
optimizing 	0.000011
statistics 	0.000027
preparing 	0.000016
executing 	0.000019
end 	0.000006
query end 	0.000001
freeing items 	0.000085
logging slow query 	0.000001
cleaning up 	0.000002

1 	SIMPLE 	p2c 	index 	PRIMARY 	PRIMARY 	8  	NULL 	19966 	Using index; Using temporary; Using filesort
1 	SIMPLE 	p 	ref 	PRIMARY 	PRIMARY 	4 	oscommerce2.p2c.products_id 	1 	Using where
1 	SIMPLE 	cd 	eq_ref 	PRIMARY 	PRIMARY 	8 	oscommerce2.p2c.categories_id,const 	1

Und hier mal ohne order by:

Zeige Datensätze 0 - 9 (10 insgesamt, die Abfrage dauerte 0.2024 sek.)

starting 	0.000054
Opening tables 	0.000285
System lock 	0.000004
Table lock 	0.000005
init 	0.000023
optimizing 	0.000011
statistics 	0.000025
preparing 	0.000013
executing 	0.000001
Sending data 	0.000244
end 	0.000004
query end 	0.000001
freeing items 	0.000085
logging slow query 	0.000001
cleaning up 	0.000002

1 	SIMPLE 	p2c 	index 	PRIMARY 	PRIMARY 	8  	NULL 	19966 	Using index
1 	SIMPLE 	p 	ref 	PRIMARY 	PRIMARY 	4 	oscommerce2.p2c.products_id 	1 	Using where
1 	SIMPLE 	cd 	eq_ref 	PRIMARY 	PRIMARY 	8 	oscommerce2.p2c.categories_id,const 	1

hat jemand eine Idee?

Gruss und schöne Nacht noch

 

[Yaslaw]

Performance-Verbesserung geht mit Try and Error. Will sagen, es gibt kein Allerweltsmittel.

Ideen:
1) Index auf p.products_date_added
2) Die Daten Vorfiltern
3) Zuerst Die Daten zusammenführen und das Resultat als Subquery nehmen zum Sortieren
4) AND (-- OR --)) durch IN(.., ...) ersetzen

Hier mal eine Kombination von 2,3,4

SELECT *
FROM
	(SELECT 
		cd.categories_name, 
		p.section_id, 
		p.products_id, 
		p.products_image, 
		p.products_tax_class_id, 
		p.products_price
	FROM 
		(SELECT * FROM products WHERE products_status = '1'AND section_id IN ('0', '13')) AS p, 
		products_to_categories AS p2c, 
		(SELECT categories_name categories_id FROM categories_description WHERE cd.language_id = '2') AS cd
	WHERE 
		p.products_id = p2c.products_id
		AND p2c.categories_id = cd.categories_id) AS data
ORDER BY products_date_added DESC

Es kann gut sein, dass dies noch langsamer ist. Aber da ich deine Tabellen und indexe nicht kenne, ist es ei Schuss ins Blaue

 

[Alex F.]

Generell ist halt zu sagen dass Sortierungen ab einer gewissen Tabellen bzw. Speichergrösse in "temp" Tabellen ausgelagert werden. Dies sind dann Operationen die nicht mehr "nur" im Arbeitsspeicher des Servers gemacht werden können und entsprechend File I/O brauchen. Dies verlangsamt natürlich dann entsprechend deine Queries.
Grüsse bb

 

[xtramen01]

Hallo,

die Abfrage dauert genauso lange.
Wie geschrieben: nehme ich order by raus dann gehts ratzfatz.

Die Tabelle sieht folgendermaßen aus:

CREATE TABLE IF NOT EXISTS `products` (
  `products_id` int(11) NOT NULL AUTO_INCREMENT,
  `products_quantity` int(4) NOT NULL,
  `products_model` varchar(12) DEFAULT NULL,
  `products_image` varchar(64) DEFAULT NULL,
  `products_price` decimal(15,4) NOT NULL,
  `products_date_added` datetime NOT NULL,
  `products_last_modified` datetime DEFAULT NULL,
  `products_date_available` datetime DEFAULT NULL,
  `products_weight` decimal(5,2) NOT NULL,
  `products_status` tinyint(1) NOT NULL,
  `products_tax_class_id` int(11) NOT NULL,
  `manufacturers_id` int(11) DEFAULT NULL,
  `products_ordered` int(11) NOT NULL DEFAULT '0',
  `section_id` int(11) NOT NULL DEFAULT '0',
  `ppp_id` int(11) NOT NULL,
  `grundpreis_menge` varchar(50) NOT NULL,
  `grundpreis_id` int(11) NOT NULL,
  `grundpreis` decimal(15,4) NOT NULL,
  `lieferstatus_id` int(11) NOT NULL,
  `bundle_id` int(11) NOT NULL,
  `products_index` int(1) DEFAULT NULL,
  `products_download` int(1) DEFAULT NULL,
  `products_download_count` varchar(50) DEFAULT NULL,
  `products_download_datei` varchar(100) NOT NULL,
  `products_download_maxdays` varchar(10) NOT NULL,
  `customers_groups_price_22` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `customers_groups_price_23` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `customers_groups_price_25` decimal(15,4) NOT NULL DEFAULT '0.0000',
  PRIMARY KEY (`products_id`,`section_id`),
  KEY `idx_products_model` (`products_model`),
  KEY `idx_products_date_added` (`products_date_added`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19988 ;

Irgend einen grund muss es ja haben dass bei Sortierung die Abfrage soviel länger dauert.
An anderen Stellen ist es genauso und ich komm einfach nicht dahinter.

Werd nochmal bissle googeln.

Vielleicht gibts ja hier noch weitere Vorschläge

Bis dahin! Gruss!

 

[Yaslaw]

Generell ist halt zu sagen dass Sortierungen ab einer gewissen Tabellen bzw. Speichergrösse in "temp" Tabellen ausgelagert werden. Dies sind dann Operationen die nicht mehr "nur" im Arbeitsspeicher des Servers gemacht werden können und entsprechend File I/O brauchen. Dies verlangsamt natürlich dann entsprechend deine Queries.
Grüsse bb

darum ists langsam....

Was für eine Datenbank ist es? MySQL? Oracle?
Wie häufig werden Daten geändert?
Je nachdem gibts noch andere Möglichkeiten

 

[CPoly]

Ich habe bei mir mal festgestellt, das Sortierung nach Datum/Uhrzeit extrem langsam ist. Als ich dann gemerkt hab, dass ich (in meinem Fall) auch einfach nach dem Primärschlussel sortieren kann, war das Problem gelöst. Geht das bei dir nicht auch products_id anstatt products_date_added?

 

[xtramen01]

Hi,

Sorry den Beitrag von brainbyte hab ich gar nich gesehen.
Aber es sind doch "nur" 20.000 Einträge. Das sollte doch kein problem für eine MySQL DB sein.

Wenn ich eine Abfrage simuliere welche 40.000 Datensätze abfrägt mit Sortierung dann gehts in 0.0005 sekunden.

select * from categories_description order by categories_name

Ich verwende MySQL v. 5.1.37.
Die Daten werden fast täglich geändert.

Die Sortierung mit dem Primary key geht auch so langsam.

Gruss

 

[Yaslaw]

Dann geht mein Fragebogen mal weiter *g*

Wie schnell geht der gleiche Sort auf die Tabelle ohne dir Verknüpfungen?

Wenn die Daten nur sporadisch geändert werden, lohnt es sich eventuell eine "Materialized View" zu simulieren. Sprich nach der Datenänderung die gesamten Daten der deines Queries in eine eigene Tabelle zu schreiben. (Google mal nach 'materialized view mysql') In der MySQL-Doku die Seite http://dev.mysql.com/doc/refman/5.0/en/create-view.html mit der Textsuche des Browsers nach 'materialized' suchen....

 

[xtramen01]

Die Abfrage mit sort der gleichen Tabelle ohne Verknüpfungen geht 0.0004 sek.

Werd ich mal nachschauen danke.

Ich hab den Tipp bekommen mehrspaltige Index zu setzen.
Und zwar in jeder Spalte die nach der Where Klausel kommt.

Hat aber nix gebracht ausser das es noch langsamer wurde.
Es werden ja sowieso fast nur IDs abgefragt die einen Primary Key haben, für den ich ja keinen Index anlegen muss.
Und für alles andere ist jeweils ein Index definiert.

Gibt es da einen Unterschied ob man die Indiez mehrspaltig macht oder ob ich für jede Spalte einen eigenen anlege?

Gruss

 

[Yaslaw]

Ja, gibt es.
wenndu auf mehrere Spalten prüfst, lohnt sich ein Index über diese Spalten. Denn dann kann MySQL mit den Werten in einem Index suchen. Ansonsten muss er merhere Indexe durchsuchen, diese wieder kombinieren etc.