[mysql] slow query

[megamuch]

SQL:

SELECT id, title FROM articles ORDER BY id ASC;

Er staat een auto_inc + unique index op id.

De output van explain {query} is: Using Filesort en dus komt hij in m'n slow query log terecht.

De tabel is ongeveer 300 records groot, dus tis niet zo heel erg, maar als ik een index leg op ID dan verwacht ik dat hij de index gebruikt en dus ook geen filesort nodig heeft.

Ik zal wel wat verkeerds doen, maar heb geen idee wat.

iemand die me een duwtje in de juiste richting kan geven?

[Tux]

En als je id de primary key maakt?

[Verwijderd]

http://dev.mysql.com/doc/internals/en/filesort.html

Volgens mij is het wel goed zo?

[megamuch]

Dan leg ik toch 2 indexen op dezelfde column?

Een Primary en een Unique??!?! Anyway, heb het geprobeerd, maar geen resultaat. Nog steeds using filesort.

Laat ik echter title uit de query weg, dan gebruikt mysql idd de index. Een index op title en id zou misschien ook nog kunnen...

Tux schreef op vrijdag 27 januari 2006 @ 14:04:
En als je id de primary key maakt?

[ Voor 21% gewijzigd door megamuch op 27-01-2006 14:10 ]

[Bosmonster]

Wat is er mis met 'using filesort'

En puur dat gebruiken om je query te classificeren als langaam lijkt me ook wat overkill. Hoe lang duurt je query eigenlijk in het echt?

En de unique kun je uiteraard verwijderen als je er een PK op legt.

[ Voor 89% gewijzigd door Bosmonster op 27-01-2006 14:11 ]

[DDemolition]

Kun je wel een unique en een key op je id leggen?
phpmyadmin 2.7x pikt dit niet volgens mij, want dit is automatisch al zo.
Verder maak ik van de ID altijd een Primarykey (int veld van 10 oid), vervolgens een auto increment erop gooien.

[ Voor 45% gewijzigd door DDemolition op 27-01-2006 14:12 ]

[megamuch]

Bosmonster schreef op vrijdag 27 januari 2006 @ 14:10:
Wat is er mis met 'using filesort'

En puur dat gebruiken om je query te classificeren als langaam lijkt me ook wat overkill. Hoe lang duurt je query eigenlijk in het echt?

Nou ja.. het komt in m'n slow query log van Mysql terecht. En aangezien ik die logfile op dit moment aan het analyseren ben om alle queries die ik uitvoer te verbeteren dmv indexen, wilde ik deze query ook optimalizeren.

Kijk de query duurt niet lang... 300 records sorteren duurt nooit lang, maar als het sneller kan, dan altijd ofcourse.

En ik was altijd van mening dat "using filesort " niet juist was mbt indexen, maar jij zegt dat er niks mis met "using Filesort" is.

[ Voor 7% gewijzigd door megamuch op 27-01-2006 14:49 ]

[whoami]

Een primary key is zowiezo een unique index.

Trouwens, het plaatsen van een index is niet altijd een garantie dat die index daadwerkelijk zal gebruikt worden. Het is ook niet altijd zo dat gebruik maken van een index de efficienste manier is om een query uit te voeren.
De meeste DBMS'en zijn intelligent genoeg om te bepalen welk execution plan het beste is, en in sommige gevallen kan het zo zijn, dat het beter is om de index niet te gebruiken.

[ Voor 83% gewijzigd door whoami op 27-01-2006 14:50 ]

[Verwijderd]

Geen MySQL kennis, maar normaliter zou ik dan de tweede kolom includen in de index...
De index wordt gebruikt om de rijen te sorteren, maar de server moet dan nog steeds op zoek gaan naar de waarde van de tweede kolom. Als je die ook in de index zet, dan hoeft dat niet meer te gebeuren en daalt (in theorie ) het aantal IO-acties. De optimizer vind dan in veel gevallen het sneller om dan geen indexen te gebruiken (het resultaat met 300 rijen past toch makkelijk in het werkgeheugen/cache)...

Maar zoals ik zei, heb alleen MSSQL-kennis, geen MySQL.

[ Voor 18% gewijzigd door Verwijderd op 27-01-2006 15:03 ]

[Varienaja]

megamuch schreef op vrijdag 27 januari 2006 @ 14:02:

SQL:

SELECT id, title FROM articles ORDER BY id ASC;

Je haalt de volledige tabel over, logisch dat er geen index gebruikt wordt.

Het lijkt me dat als je id primary key maakt + een index daarop legt, dat je dan de snelste methode hebt om alle records gesorteerd en wel op te halen met deze query.

(Primary key is vaak een hash-achtig iets, vandaar dat het soms loont om er nog een index op te zetten).

Verwijderd schreef op vrijdag 27 januari 2006 @ 15:00:
De index wordt gebruikt om de rijen te sorteren, maar de server moet dan nog steeds op zoek gaan naar de waarde van de tweede kolom. Als je die ook in de index zet, dan hoeft dat niet meer te gebeuren en daalt (in theorie ) het aantal IO-acties
[knip]
Maar zoals ik zei, heb alleen MSSQL-kennis, geen MySQL.

Een rij is een rij hoor. Het zou toch al te mal zijn dat ieder rijen kolom voor kolom bij elkaar gequeried zouden worden door het DBMS. ;-)

[ Voor 36% gewijzigd door Varienaja op 27-01-2006 15:13 ]

[kenneth]

Varienaja schreef op vrijdag 27 januari 2006 @ 15:11:
Een rij is een rij hoor. Het zou toch al te mal zijn dat ieder rijen kolom voor kolom bij elkaar gequeried zouden worden door het DBMS. ;-)

Als je alleen geindexeerde velden selecteert, hoeft er niet meer in de tabel gekeken worden, dat is wel degelijk sneller.

[Bosmonster]

Varienaja schreef op vrijdag 27 januari 2006 @ 15:11:
[...]

Je haalt de volledige tabel over, logisch dat er geen index gebruikt wordt.

En het sorteren gebruikt geen indices?

[whoami]

Verwijderd schreef op vrijdag 27 januari 2006 @ 15:00:
Geen MySQL kennis, maar normaliter zou ik dan de tweede kolom includen in de index...
De index wordt gebruikt om de rijen te sorteren, maar de server moet dan nog steeds op zoek gaan naar de waarde van de tweede kolom. Als je die ook in de index zet, dan hoeft dat niet meer te gebeuren en daalt (in theorie ) het aantal IO-acties. De optimizer vind dan in veel gevallen het sneller om dan geen indexen te gebruiken (het resultaat met 300 rijen past toch makkelijk in het werkgeheugen/cache)...

Maar zoals ik zei, heb alleen MSSQL-kennis, geen MySQL.

In MS-SQL zou je een clustered index kunnen zetten op dat id veld (mits dat id-veld nooit veranderd -wat een id veld nooit zou moeten doen- en, mits het id veld geen GUID veld is, maar een identity bv.
Dan heb je direct de snelste manier om die query uit te voeren. Je clustered index bepaald nl. de fysieke opslagvolgorde van je records, dus, je kan hier gewoon de tabel van voor naar achter uitlezen, en hij is al direct gesorteerd.

[Varienaja]

Bosmonster schreef op vrijdag 27 januari 2006 @ 15:31:
En het sorteren gebruikt geen indices?

Als er een index op id was, en je sorteert op id, dan kan de index gebruikt worden. Dat zeg ik toch ook:

Het lijkt me dat als je id primary key maakt + een index daarop legt, dat je dan de snelste methode hebt om alle records gesorteerd en wel op te halen met deze query.

[Varienaja]

kenneth schreef op vrijdag 27 januari 2006 @ 15:14:
Als je alleen geindexeerde velden selecteert, hoeft er niet meer in de tabel gekeken worden, dat is wel degelijk sneller.

Staan alle velden dan dubbel in de database? Een keer in de tabel, en een keer in de index? Dat wist ik niet. Ik heb zelf wel eens een database-je geprogrammeerd, en dan zaten alle gegevens in de tabel, en was de index niet meer dan een B-boom met recordpointers die naar rijen in de tabel wezen. Ik dacht altijd dat dat bij echte databases ook wel zo zou werken. :-)

[Janoz]

Niet alle velden. Vandaar ook dat kenneth het over "Als je alleen geindexeerde velden selecteert" heeft. Als je alleen een recordset met ID's ophaalt heb je enkel aan de B-boom genoeg. Daar staan die ID's immers wel in.

[Bosmonster]

Varienaja schreef op vrijdag 27 januari 2006 @ 16:36:
[...]

Als er een index op id was, en je sorteert op id, dan kan de index gebruikt worden. Dat zeg ik toch ook:


[...]

Een PK (in MySQL tenminste) is gewoon een index voor zover ik weet. Daar ook nog een keer een losse index op leggen is dus dubbelop.

[Varienaja]

Bosmonster schreef op vrijdag 27 januari 2006 @ 16:51:
Een PK (in MySQL tenminste) is gewoon een index voor zover ik weet. Daar ook nog een keer een losse index op leggen is dus dubbelop.

OK. Ik had in Postgres een keer trage resultaten, en dat bleek te komen doordat m'n PK daar een hash was.

"where id=6" gaat dan wel snel, maar "where id>6" niet zo :-D