[MySQL] Using where; Using index; Using filesort

[TheNephilim]

Nou de titel spreekt voor zich denk ik! In een oud project kwam ik een query tegen die in een EXPLAIN een 'Using where; Using filesort' teruggaf. Daar kon ik zelf nog wat aan veranderen om er een 'Using where; Using index; Using filesort' van te maken, maar mooi is het nog steeds niet. Hieronder de query (in essentie).

MySQL:

SELECT 
    topic_id
FROM
    fora_topics
        
WHERE
    forum_id=49

ORDER BY 
    topic_sticky DESC,
    topic_closed = 1 ASC,
    last_post_id DESC

De index die er gebruikt word ziet er zo uit, in deze volgorde:

code:

forum_id
topic_sticky
topic_closed
topic_post // word gebruikt in een INNER JOIN
last_post_id

Wat kan ik doen om deze query nog enigszins te optimaliseren? Maakt volgorde nog uit voor de index of moet misschien topic_post er tussenuit?

Via Google vind ik vooral veel oude blogposts en dergelijke; 2006, 2009 soms 2012. Weinig eenduidig en na wat proberen nog niks wijzer

[Keeper]

De belangrijkste vraag is, moet deze query worden geoptimaliseerd? Hoe lang duurt hij nu? Hoe vaak wordt hij aangeroepen? Is dat een performance issue? Of is het gewoon een prima performende query en wil je heel graag die filesort er uit hebben?

[TheNephilim]

Keeper schreef op dinsdag 24 februari 2015 @ 10:56:
De belangrijkste vraag is, moet deze query worden geoptimaliseerd? Hoe lang duurt hij nu? Hoe vaak wordt hij aangeroepen? Is dat een performance issue? Of is het gewoon een prima performende query en wil je heel graag die filesort er uit hebben?

De EXPLAIN geeft aan dat er ruim 240.000 rows zijn en de query duurt inderdaad meerdere seconden.

[Anoniem: 566595]

Begrijp ik het goed dat je 1 index hebt op 5 kolommen? Of is dit alleen de relevante index? Zonder explain plan blijft het gokken.

Volgens de boekjes moet de index bestaan uit where + order by dus dan moet topic_post er uit idd.

[Gomez12]

Gevoelsmatig zou ik zeggen dat je in je index de plaats van je topic_post anders moet (nu staat hij tussen 3 order by variabelen, dat is imho ietwat raar, inner joins zullen of voor of na de order by worden afgehandeld maar de kans dat dit ergens ertussenin gebeurt lijkt me vrij klein)

Daarnaast weet ik even niet of MySQL ook inverted indexes kent, want 2 van je order by variabelen zijn desc gesorteerd ik betwijfel of mysql wel binnen 1 index en ascending en descending kan zoeken.

Daarnaast ben ik niet bekend met je notatie :

SQL:

ORDER BY 
    topic_closed = 1 ASC,

Ik weet niet zo direct wat het moet doen, maar ik vermoed wel dat het invloed heeft op je index-gebruik.

Maar ik vermoed dat het grootste obstakel hem zit in het desc asc desc orderen van je resultset, ik vermoed dat die het verdere index-gebruik stopt

[TheNephilim]

Anoniem: 566595 schreef op dinsdag 24 februari 2015 @ 21:29:
Begrijp ik het goed dat je 1 index hebt op 5 kolommen? Of is dit alleen de relevante index? Zonder explain plan blijft het gokken.

Volgens de boekjes moet de index bestaan uit where + order by dus dan moet topic_post er uit idd.

Hier een screenshot van de EXPLAIN. Er zijn inderdaad meerdere indexen, maar de gebruikte heet forum_id_3.



Dat was ook mijn eerste idee, om die topic_post eruit te halen, alleen word die gebruikt in een INNER JOIN.

Gomez12 schreef op woensdag 25 februari 2015 @ 01:27:
Gevoelsmatig zou ik zeggen dat je in je index de plaats van je topic_post anders moet (nu staat hij tussen 3 order by variabelen, dat is imho ietwat raar, inner joins zullen of voor of na de order by worden afgehandeld maar de kans dat dit ergens ertussenin gebeurt lijkt me vrij klein)

Dus volgens jou zou topic_post achteraan moeten staan?

Daarnaast weet ik even niet of MySQL ook inverted indexes kent, want 2 van je order by variabelen zijn desc gesorteerd ik betwijfel of mysql wel binnen 1 index en ascending en descending kan zoeken.

Goh dat is lang geleden, maar waarschijnlijk niet. Het is zo lastig dat zulke dingen nergens echt duidelijk vermeld staan. Dat word weer neuzen in de ellenlange documentatie denk ik.

Daarnaast ben ik niet bekend met je notatie :

SQL:

ORDER BY 
    topic_closed = 1 ASC,

Ik weet niet zo direct wat het moet doen, maar ik vermoed wel dat het invloed heeft op je index-gebruik.

Die zit er in om alle gesloten topics onderaan te plaatsen. De volgorde van de ORDER BY is ook belangrijk.

[Gomez12]

Ik zou in eerste instantie eens een explain proberen op een andere query om te zien wat er gebeurt als je het exact volgens de index doet.

Dus bijv :

SQL:

SELECT 
    topic_id
FROM
    fora_topics
        
WHERE
    forum_id=49

ORDER BY 
    topic_sticky ASC,
    topic_closed  ASC,
    last_post_id ASC

Wat is het resultaat daarvan? Want die moet redelijk je index volgen.

Daarnaast is het maar net de vraag of je wel echt 240.000 rijen wilt terugkrijgen (en hoeveel staan er in de tabel), dat aantal kan er ook voor zorgen dat je query optimizer besluit geen index te pakken.
Als jij 99% van een tabel wilt hebben dan zal menig query-optimizer zeggen : Ga maar naar filesort, want dat moet er toch uiteindelijk gebeuren

[TheNephilim]

Gomez12 schreef op donderdag 26 februari 2015 @ 10:20:
Ik zou in eerste instantie eens een explain proberen op een andere query om te zien wat er gebeurt als je het exact volgens de index doet.

Dus bijv :

SQL:

SELECT 
    topic_id
FROM
    fora_topics
        
WHERE
    forum_id=49

ORDER BY 
    topic_sticky ASC,
    topic_closed  ASC,
    last_post_id ASC

Wat is het resultaat daarvan? Want die moet redelijk je index volgen.

Nog steeds Using where; Using index; Using filesort. Dus het zou dan echt die ene column in de index (topic_post) moeten zijn.

Daarnaast is het maar net de vraag of je wel echt 240.000 rijen wilt terugkrijgen (en hoeveel staan er in de tabel), dat aantal kan er ook voor zorgen dat je query optimizer besluit geen index te pakken.
Als jij 99% van een tabel wilt hebben dan zal menig query-optimizer zeggen : Ga maar naar filesort, want dat moet er toch uiteindelijk gebeuren

Er zitten ongeveer 480.000 rijen in de tabel fora_topics, dus ongeveer 50% van het totale aantal topics. Maar inderdaad, wellicht is het de optimizer; zou goed kunnen gezien de brakke databasestructuur etc.

[Voutloos]

Op basis van gegeven info en wellicht incompete query kunnen we alleen maar gokken. Mijn gok is dan dat topic_post direct na forum_id in de index moet.