✔ Performanceverlust bei Abfrage unter Verwendung von order by

Hi, versuch mal :

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

hierzu machst du folgende indizes

auf products -> id,product_status,section_id
auf products_to_categories -> products_id
auf categories_description -> id, language_id

das sollte bei so kleinen mengen schon reichen. Wenn sich das EXPLAIN und die Laufzeit nicht bessern, musst du mit USE_INDEX in der TABLES-CLAUSE die Indizes vorgeben, evtl. grützt der "Optimizer" von MySQL da herum (der zieht per standard immer den kleinsten Index zuerst an, was nicht wirklich "optimieren" bedeutet).

grüße
gore
 
Sodele!

Ich schuld euch allen ein Bier :-)

Ich hab die Indizes laut gorefest gesetzt und siehe da 0.0007 sek.
Das ist optimal!!

Ich verstehe das aber nich ganz. Ich dachte man muss keinen Index setzen für Spalten bei denen ich nach dem PRIMARY Key suche oder sortiere.

Vorher waren die Indizes, bis auf die id's welche ja den PRIMARY haben, genauso gesetzt.

Kannst du mir das kurz erläutern warum das jetzt soviel schneller läuft?

Gruss und vielen dank!!
 
Das ist ganz einfach:

Alle Spalten, die über eine WHERE Klausel eingeschränkt werden müssen mit den Index, weil der Index einen Suchbaum bildet.

Du kannst Relationen von verschiedenen Richtungen JOINen. Wenn Du nur die Schlüssel indizierst, werden die nachfolgenden Kriterien auf den Ergebnissen angewandt und nicht mehr indiziert abgefragt. Ein Optimizer kann entscheiden, den Einstieg ins Mengengerüst zu ändern - daher mein Hinweis mit USE INDEX, womit man den Optimizer dazu zwingt, wie gefordert vorzugehen.

Wenn nur die Primärschlüssel indiziert sind, kann der Server die WHERE Klausel also indiziert abfragen. Der Unterschied schlägt sich dann im JOINen der Tabellen durch, welches dann nicht mehr logaritmischen, sondern linearen Aufwand verursacht. Das Indizieren einzelner Spalten ist wirkungslos, da die Einschränkungen bei Deinen Tabellen immer (KEY | FOREIGN_KEY, WHERE_CRIT1,...) sind. Einen signifikanten Gewinn an Performance bekommst Du nur, indem Du dafür sorgst, dass Deine jeweilige Kriterienmenge immer einen Index aufweist, und zwar mit der Spalte am Anfang, welche dem Einstiegspunkt enspricht.

+ Ergo ist es ein guter Schritt einen Index wie folgt zu planen : Fremdschlüseel, spalte mit wenig inhaltsvarianz (Am besten sind da int-felder oder (mysql-spezifisch) enums), spalte mit mehr inhaltsvarianz (varchars etc).

+ INNER JOIN signalisiert (zumindest bei Postgres) darüber hinaus das Vorhandensein eines Fremdschlüsselindexes.

+ ein weiterer Trick ist die Vermeidung von OR-Clauses, wenn man abzählbare Werte hat (IN ist da besser, weil kein OR - sprich mengenaddition - sondern ein AND -also eine indexfähige mengeneinschränkung- stattfindet) .

WICHTIG : bei mySQL sind gegenläufig gebaute Indizes wirkungslos (SpalteA asc, SpalteB desc t net).

Btw, Performancetuning ist mit nichten nur trial and error, sondern im Großen und Ganzen gesunder Menschenverstand, denn ca 50% der Performancetricks sind unabhängig vom Server. O.g. Tuningmaßnahme hat mir sehr hoher Wahrscheinlichkeit auch einen positiven Effekt in Oracle oder Postgres.

Grüße
gore
 
Zuletzt bearbeitet:
Ok Super für die ausführliche Erklärung.

Aber es ist ein wenig viel Info für mich der sich nich unbedingt so toll damit auskennt.
Meinst du, es wäre möglich mir das nochmal anhand eines Beispieles zu erklären, wie und warum ich die Indizes setzen muss?

Dann kann ich die beiden Fälle miteinander vergleichen. Bin da eher ein praktiker als ein Theoretiker :-).

Aber ich fands sehr lerreich!.

Wie gesagt ich muss nur noch rausfinden wie man die Indizes richtig setzt und vorallem warum.

Hier wäre das konkrete Beispiel eienr Abfrage die auch sehr langsam läuft:

Code: 
SELECT count( p.products_id ) AS total
FROM products_description pd, products p
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
LEFT JOIN specials s ON p.products_id = s.products_id, products_to_categories p2c
WHERE p.products_status = '1'
AND p.products_id = p2c.products_id
AND pd.products_id = p2c.products_id
AND pd.language_id = '2'
AND p2c.categories_id = '52'
AND (
p.section_id = '0' || p.section_id = '13'
)

Gruss und vielen Danke für die Mühe!
 
SQL: 
SELECT count( p.products_id ) AS total
FROM products_description pd, products p
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
LEFT JOIN specials s ON p.products_id = s.products_id, products_to_categories p2c
WHERE p.products_status = '1'
AND p.products_id = p2c.products_id
AND pd.products_id = p2c.products_id
AND pd.language_id = '2'
AND p2c.categories_id = '52'
AND (
p.section_id = '0' || p.section_id = '13'
)

Hi, zunächst mal

SQL: 
p.section_id = '0' || p.section_id = '13'
umwandeln in
SQL: 
p.section_id IN ('0' , '13')

dann : sofern die LEFT JOINs nicht zwingend notwendig sind -> umwandeln in INNER JOINs

den Equijoin am Ende von

SQL: 
LEFT JOIN specials s ON p.products_id = s.products_id, products_to_categories p2c

und in

SQL: 
FROM products_description pd, products p

umwandeln in INNER JOIN

und die Tabelle Manfacturers und Specials rauswerfen (wird doch nirgendswo benutzt? *knickinderoptikhab*)

Wenns dann noch nicht schnell ist, mach ein FROM products p USE INDEX (Dein_products_index_name_von_vorhin)

Damit sollte es gehen. Wenn nicht, poste mal das EXPLAIN.

Grüße
gore
 
Zuletzt bearbeitet von einem Moderator:
Hi,

Es lag an den LEFT JOINS.
Allerdings soweit ich gesehen habe müssen die so sein. Die Ergebnisse werden sonst nicht angezeigt.
Ich schau mal ob ich die Abfrage anders gestalten kann.

Jedenfalls hab ich wiedermal einiges gelernt Heute! :-)

Gruss und vielen Dank!
 
Leider zu frü gefreut! :-(

Ich muss die Abfrage so lassen.
Kein Wunder das es mit INNER JOIN so schnell geht. Bekomme ja auch kein Ergebnis.

Ich blick einfach nich die Indizes richtig zu setzen.
Bei der vorigen Abfrage klappts ja Wunderbar.

Ich hoffe ich kann deine Zeit nochmal kurz in Anspruch nehmen. Bin am verzweifeln...

Vielleicht siehst du nochmal was.

Code: 
EXPLAIN SELECT count( p.products_id ) AS total
FROM products_description pd, products p
LEFT JOIN manufacturers m ON p.manufacturers_id = m.manufacturers_id
LEFT JOIN specials s ON p.products_id = s.products_id, products_to_categories p2c
WHERE p.products_status = '1'
AND p.products_id = p2c.products_id
AND pd.products_id = p2c.products_id
AND pd.language_id = '2'
AND p2c.categories_id = '52'
AND p.section_id
IN (
'0', '13'
)

Code: 
1 	SIMPLE 	p2c 	index 	PRIMARY,e_index 	PRIMARY 	8  	NULL 	19966 	Using where; Using index
1 	SIMPLE 	p 	ref 	PRIMARY,e_index 	e_index 	5 	oscommerce2.p2c.products_id,const 	1 	Using where; Using index
1 	SIMPLE 	m 	eq_ref 	PRIMARY 	PRIMARY 	4 	oscommerce2.p.manufacturers_id 	1 	Using index
1 	SIMPLE 	s 	index 	NULL 	PRIMARY 	8 	NULL 	1 	Using index
1 	SIMPLE 	pd 	eq_ref 	PRIMARY 	PRIMARY 	8 	oscommerce2.p.products_id,const 	1 	Using where; Using index

Code: 
starting 	0.000082
Opening tables 	0.000026
System lock 	0.000005
Table lock 	0.000006
init 	0.000030
optimizing 	0.000016
statistics 	0.000052
preparing 	0.000022
executing 	0.000003
Sending data 	0.220083
end 	0.000005
query end 	0.000002
freeing items 	0.000053
logging slow query 	0.000001
cleaning up 	0.000002

Grüsse!
 
Momentchen, wenn der inner join net t, dann fehlen irgendwo daten !?


Code: 
EXPLAIN 
SELECT count( p.products_id ) AS total
FROM products p INNER JOIN products_description pd
     ON   p.products_id = pd.products_id
INNER JOIN products_to_categories p2c
    ON  p.products_id = p2c.products_id
WHERE p.products_status = '1'
AND pd.language_id = '2'
AND p2c.categories_id = '52'
AND p.section_id IN ('0', '13')

muss technisch dasselbe ergebnis geben, ansonsten sind in einer deiner tabellen die daten grütze bzw. die keys faul.

ein left join selektiert nämlich auch etwas ohne auf der anderen partnerseite. deswegen kriegst du auch damit daten.

grüße,
gore
 
Sorry ich sitze schon solange drann das ich gar nimma richtig denken kann.
War mein Fehler!
Ich hab die Abfrage nicht richtig umgebaut sondern aus LEFT lediglich INNER gemacht.
Logisch das dies nich geht.

Ich weis auch nimma weiter.

Ich hab die Abfrage nun mal auf das mindeste reduziert so nach und nach.
Und es machen sich keine signifikanten Änderungen bemerkbar.

Code: 
SELECT count( products_id ) AS total
FROM products_description USE INDEX (language_id)
WHERE language_id = '2'

Code: 
starting 	0.000039
Opening tables 	0.000009
System lock 	0.000003
Table lock 	0.000004
init 	0.000015
optimizing 	0.000006
statistics 	0.000043
preparing 	0.000007
executing 	0.000003
Sending data 	0.116507
end 	0.000005
query end 	0.000003
freeing items 	0.000047
logging slow query 	0.000001
cleaning up 	0.000002

Und die Abfrage mal ohne COUNT, wobei die Zugriffszeit optimal ist:

Code: 
SELECT *
FROM products_description USE INDEX (language_id)
WHERE language_id = '2'

Code: 
starting 	0.000036
Opening tables 	0.000008
System lock 	0.000010
Table lock 	0.000004
init 	0.000017
optimizing 	0.000005
statistics 	0.000045
preparing 	0.000008
executing 	0.000002
Sending data 	0.000427
end 	0.000002
query end 	0.000001
freeing items 	0.000024
logging slow query 	0.000001
cleaning up 	0.000001

geht die Verwendung von COUNT so zu lasten der Performance?

Gruss
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück