[MySQL] Query met order by gebruik index niet

[Xcalibur]

Ik heb een probleem met een query die erg traag wordt omdat hij een index niet gebruikt. Ik heb me al uitgebreid ingelezen in indices etc, ook heb ik al een vergelijkbaar topic gevonden (Mysql Traag door order by Desc), maar toch kom ik er nog niet uit

De query bevat wat joins, where-clausules en een ORDER BY DESC, maar als ik die allemaal verwijder blijf ik tegen (in mijn ogen) iets vreemds aanlopen.

---

code:

SELECT gtin FROM articles ORDER BY id

Dit levert de volgende EXPLAIN op:

code:

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | articles | ALL | NULL | NULL | NULL | NULL | 585339 | Using filesort

Er bestaan indices op id (PRIMARY) en gtin (INDEX).
Deze worden hier niet gebruikt, ondanks de ORDER BY id. Ook bij een SELECT * worden deze niet gebruikt.

Als ik echter niet de gtin maar id SELECT, wordt de index wél gebruikt:

code:

SELECT id FROM articles ORDER BY id

code:

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | articles | index | NULL | PRIMARY | 3 | NULL | 585339 | Using index

of

code:

SELECT gtin FROM articles ORDER BY gtin

code:

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | articles | index | NULL | gtin | 768 | NULL | 585339 | Using index

Als ik een index op op (id,gtin) aanmaak en de eerste query weer draai wordt deze nieuwe index wél gebruikt.

code:

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | articles | index | NULL | id_gtin | 771 | NULL | 585339 | Using index

---

Naar mijn weten wordt een index gebruikt om de juiste records op te zoeken in de tabel (op basis van de WHERE clausules) en de records te sorteren waar mogelijk (op basis van de ORDER BY clausules), maar niet om de inhoud van gevraagde kolommen op te halen binnen de geselecteerde records?

Mijn vraag: waarom is het nodig om het SELECT-veld in de index op te nemen, voordat deze gebruikt kan worden?

[Zoijar]

En met een FORCE INDEX ( http://dev.mysql.com/doc/refman/5.0/en/index-hints.html ) erbij? Optimizer snapt blijkbaar niet precies wat je wilt.

Het waarom moet ik je schuldig blijven. Misschien denkt hij dat het sneller is om in een keer een hele kolom binnen te halen en dan te sorteren dan een index af te lopen en daarbij dan een ander veld op te halen. Bulk/streaming I/O oid, geen idee.

Ik bedoel stel dat je een index hebt als in (id, block index): (0,3) (1,5) (2,4), (3,0) Dan kan je die aflopen omdat id gesorteerd is, dan het block voor het record ophalen en daar je select uit lezen. Je disk I/O gaat dan in block volgorde 3,5,4,0. Als je dat niet eerst opslaat en dan sorteert, heb je dus elke keer een disk seek. Als je het wel sorteert had je net zo goed direct het veld kunnen ophalen en die lijst sorteren. Dus je leest die hele kolom sequentieel uit met 0 seeks, buffert het gewenste veld tijdelijk in geheugen, en sorteert dat dan. I/O-wise kan dat sneller zijn.

Als je een gecombineerde index hebt kan hij direct het tweede veld uit de index gebruiken (van de dan 3 velden) en heb je ook 0 seeks doordat de feitelijke tabel nooit gelezen hoeft te worden.

(ik type dit zo uit m'n hoofd; misschien vergis ik me en is het complete onzin, maar het klinkt voor mij logisch.... databases zijn al weer een tijdje geleden hehe)

Verder kan ik me nog voorstellen dat er ergens een hash index staat ipv een b-tree en hij die dus niet gesorteerd kan aflopen.

[ Voor 132% gewijzigd door Zoijar op 07-01-2013 21:38 ]

[Xcalibur]

Ik had al een USE INDEX geprobeerd, maar dat had geen effect.
De FORCE INDEX lijkt wel effect te hebben :-)

code:

SELECT gtin FROM articles USE INDEX ( PRIMARY ) ORDER BY id

code:

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | articles | index | NULL | PRIMARY | 3 | NULL | 585339 |

Alleen "Extra" blijft nu leeg? Maar dat kan geen kwaad denk ik, de query is wel snel nu

Het waarom blijft me een raadsel....Ik hoop dat ik op deze manier m'n originele query weer kan bouwen, zonder dat hij weer vervelend gaat lopen doen....

[Zoijar]

Time het wel even goed, want je optimizer is in veel gevallen helemaal niet zo dom. Maar misschien dat hij het hier in dit specifieke geval niet goed doet.

[Gomez12]

Je tabel al eens opnieuw laten analyzen? Wellicht zijn de internal metrics ietwat off.

[__fred__]

Is dit de volledige query? Geen where statements? En welke storage engine?

In ieder geval het volgende, 10 tegen 1 dat je ID veld de clustered index is. Dit betekent dat de record data sequentieel op volgorde van insert wordt opgeslagen in de pagina's van de clustered index. Als je dus een pagina van de clustered index leest staat daar ook de data (op wat uitzonderingen na, zoals tekst velden en als je veel kolommen hebt, maar het gaat ff om het idee).

Sorteren op de clustered index, is dus een beetje onzin, omdat de data al in die volgorde is opgeslagen. Het volstaat dus om gewoon de pagina's met data in te lezen. Gebruik maken van de index op gtin is dus zelfs inefficient:

* De complete datapagina moet ingelezen worden voor één of meer waarden uit de clustered index, maar dan heb ik ook gelijk de data.
* hierna een lookup doen in de gtin op basis van de primary key om gtin op te halen is dubbel werk, die had ie namelijk al, toen ie de page uit de clustered index ophaalde.

[pedorus]

Met een index op (id, gtin) hoef je de originele tabel niet meer te raadplegen als je gtin gesorteerd op id terugvraagt. Met alleen een index op id heb je wel de originele tabel nodig. En wellicht betreft het zelfs een clustered index zoals hierboven wordt gesteld (InnoDB storage engine?), waardoor de data in de gewone tabel al in die volgorde staat.

Zonder limit of joins vraag je trouwens nogal ruim data op, dat is wellicht niet representatief voor je echte query. Misschien is het beter om de gehele query erbij te pakken voor optimalisatie

[Xcalibur]

Dit is inderdaad niet de volledige query, maar dit is de meest versimpelde versie waarmee ik het "probleem" kon reproduceren

Het doel van de query is om de x laatst toegevoegde artikelen te tonen (dus de hoogste ID's), dus: ORDER BY id DESC LIMIT 0,x. Ik snap waarom DESC sorteren traag is, het verbaasde me daarom des meer dat ook de ASC sortering problemen gaf....

Het hoe en wat van een clustered index is nieuw voor me, daar ga ik eens naar Googlen
Het gaat om een MyISAM tabel van zo'n 600.000 records overigens, met een kolom of 40, waaronder een paar TEXT-velden.


@pedorus: ik begrijp wat je bedoelt met dat je de originele tabel niet meer hoeft te raadplegen, als de data al in je index zit. Maar dit werkt alleen als je weinig kolommen opvraagt (of een hele grote index hebt)?

In de praktijk heb ik flink wat van de kolommen nodig (id, gtin, titel, auteur, prijs, adviesprijs, actieprijs, afbeelding, etc.). Hij zal dus altijd de originele tabel moeten raadplegen lijkt me. Maar daar is de index toch juist voor bedoeld, zodat hij WEET waar de data staat....? Maar dat mislukt nu net


@Gomez: de tabel analyzen / optimizen heeft geen effect op het gebruik van de index.

[pedorus]

Waarom zou de index perse gebruikt moeten worden? Het heeft niet altijd nut om de data niet sequentieel te lezen. Wat in ieder geval voorkomen moet worden, is dat hardeschijfkoppen teveel moeten bewegen als er data van een disk moet komen of dat solid state geheugen random wordt uitgelezen. Ook in die zin is sorteren in het geheugen helemaal niet gek. Sorteren kost nauwelijks tijd in verhouding tot niet-sequentieel lezen van media. Als alles toch al in het werkgeheugen staat gaat dit natuurlijk niet op.

In het draadje wat je aanhaalt staat deze opmerking:

Voutloos schreef op maandag 02 januari 2012 @ 21:28:
En in welke volgorde heb je de samengestelde index getest? En welke engine gebruik je? Indien geen innodb: Stap over naar innodb indien sneller. Indien niet sneller: stap alsnog over.

[beetle71]

Als je weet dat je ID's 'compleet' (lees: opeenvolgend) zijn is er een andere optie om onder je LIMT (en evt. je DESC )uit te komen

SQL:

     SELECT id FROM articles WHERE id>(select max(id)-10 from articles)

in mysql wordt die MAX(id) weggeoptimaliseerd en krijg je zo je laatste tien id's

[ Voor 3% gewijzigd door beetle71 op 08-01-2013 22:58 ]

[Voutloos]

In geval van MyISAM moet mysql alsnog de héle tabel erbij halen om op basis vd id index de gtin waarde op te halen. Dan is gewoon direct alles lezen efficienter, en dat gebeurt doorgaans al als je ruwweg >30% vd rows selecteert. In de docs staat een pagina over 't voorkomen van table scans, waar stiekem een aantal scenario's staat waar het niet per se slecht is: https://dev.mysql.com/doc...-to-avoid-table-scan.html

Met de gecombineerde index wordt wel de index gebruikt. Sterker nog, 'using index' in de explain betekent zelfs dat enkel de index gebruikt wordt.

Met innodb zie je misschien ook iets vaker 'using index' staan, omdat bij de secundaire indices ook de pk opgeslagen is. Maar welke engine of combinatie van indices beter is kan alleen jij zelf meten.

[Gomez12]

Xcalibur schreef op dinsdag 08 januari 2013 @ 21:59:
Dit is inderdaad niet de volledige query, maar dit is de meest versimpelde versie waarmee ik het "probleem" kon reproduceren

Het doel van de query is om de x laatst toegevoegde artikelen te tonen (dus de hoogste ID's), dus: ORDER BY id DESC LIMIT 0,x. Ik snap waarom DESC sorteren traag is, het verbaasde me daarom des meer dat ook de ASC sortering problemen gaf....

Je voorbeeld query is momenteel simpelweg verkeerd.

Als je alles selecteert (wat je nu doet) dan kan hij kiezen of alles uit de index halen omdat alle velden in die index staan (is niet zo bij gtin) of de hele index negeren omdat hij toch alles van disk af moet halen.
Zoals jij het wilt hebben (eerst volledige index sorteren en dan alle records van die index van schijf afhalen) is simpelweg trager dan direct alles van schijf afhalen.

Op het moment dat je aan die select gtin order by id een limit 10 oid zou hangen dan zou hij waarschijnlijk wel weer de index pakken, maar momenteel moet hij linksom of rechtsom toch alle records van schijf lezen en daarna sorteren, waarom dan een stap van te voren doen is de gedachte...

Btw, als je echt wilt orderen op desc id dan kan je ook simpelweg een desc index aanmaken (alhoewel ik eigenlijk nooit zou orderen op id, id is bij mij altijd een nietszeggend intern veld, na een paar updates hoeft de hoogste id helemaal niet meer het nieuwste item te zijn)

[Xcalibur]

Dank voor alle reacties

Gomez12 schreef op woensdag 09 januari 2013 @ 00:13:
[...]
Op het moment dat je aan die select gtin order by id een limit 10 oid zou hangen dan zou hij waarschijnlijk wel weer de index pakken, maar momenteel moet hij linksom of rechtsom toch alle records van schijf lezen en daarna sorteren, waarom dan een stap van te voren doen is de gedachte...

Dat gaan we eens proberen
Ik heb hier alleen pas vanavond laat of morgen tijd voor denk ik.

Gomez12 schreef op woensdag 09 januari 2013 @ 00:13:
[...]
Btw, als je echt wilt orderen op desc id dan kan je ook simpelweg een desc index aanmaken (alhoewel ik eigenlijk nooit zou orderen op id, id is bij mij altijd een nietszeggend intern veld, na een paar updates hoeft de hoogste id helemaal niet meer het nieuwste item te zijn)

Een desc index bestaat niet in MySQL begreep ik?

Mijn doel is om de nieuwste toevoegingen in de gekozen categorie te laten zien, dat zijn dus niet eens per se de x-laatste toevoegingen aan de database (behalve op de homepage). Dus waarschijnlijk kom ik er dan toch niet met zo'n index?

[Gomez12]

Xcalibur schreef op woensdag 09 januari 2013 @ 09:06:
[...]
Een desc index bestaat niet in MySQL begreep ik?

Lol, je moet van mysql blijven houden... Ik deed een snelle google actie of het ondersteunt werd en ik zag het in de resultaten naar boven komen dus ik dacht dat het ondersteunt werd....

Totdat je de pagina op https://dev.mysql.com/doc/refman/4.1/en/create-index.html zelf leest :

An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

[Voutloos]

Maar voor een index op enkele kolom werkt het gewoon prima. Enige dat niet optimaal werkt is het sorteren op meerdere kolommen met een mix van ASC en DESC.

[Elijan9]

Gomez12 schreef op woensdag 09 januari 2013 @ 11:59:
[...]

Lol, je moet van mysql blijven houden... Ik deed een snelle google actie of het ondersteunt werd en ik zag het in de resultaten naar boven komen dus ik dacht dat het ondersteunt werd....

Totdat je de pagina op https://dev.mysql.com/doc/refman/4.1/en/create-index.html zelf leest :

[...]

Een BTREE index zoals MySQL gebruikt, kun je in tegengestelde richting even efficient gebruiken, daarom wordt dit nu nog genegeerd (ook in nieuwere versies, de link naar 4.1 is wel erg oud). Voor indexen op meerdere kolomen en een mix van ASC/DESC zou het wel echt wat kunnen uitmaken als ASC/DESC niet genegeerd zouden worden, maar ook in dat geval is de BTREE index nog steeds (behoorlijk) efficient te gebruiken.