[mysql] order by optimaliseren of alternatief?

[marty]

ik heb een query waar een ORDER BY in zit en die er 10 seconde over doet! niet echt fijn. En dit ligt echt puur aan de ORDER BY want als ik die weghaal duurt ie nog maar 0.01 seconde.
Nou ben ik eens wat gaan stoeien met die query om te kijken hoe ik van die filesort af kon komen (die je met explain zichtbaar maakt) en kwam er uiteindelijk achter dat zelfs de meest simpele vorm van die query (zonder joins) nog een filesort doet, terwijl ik 'm op m'n primary key doe.
Ben toen hier eens wat met de search gaan zoeken en zag een comment van ACM staan die de verklaring gaf:

Mysql is niet echt een held met sorteren, zodra ie achterstevoren moet sorteren gebruikt ie bijv _altijd_ een filesort.

(vind het trouwens vaag dat dit niet in de manual staat, daar hebben ze het alleen over dat ie dat doet als je ASC en DESC mixed, en ik gebruik enkel een DESC - maargoed, volgens mij klopt het wel wat ACM zegt, want ik zou niet weten waarom ie anders die filesort doet.)

dit is de simepele versie van m'n query:

code:

SELECT *
FROM rbs WHERE id <= 10597
ORDER BY id DESC LIMIT 0,1

(In de echte query wordt nog van alles gejoined (de explain daarvan ziet er verder netjes uit trouwens, no problems there).)

Deze query doe ik zo omdat ik dat ID wil hebben, of, als die niet beschikbaar is (er staat in de echte query ook nog het 1 en ander in de where, wat variabel is en door een php script wordt bepaald) degene met het hoogste id daaronder.

Mijn vraag: is er echt geen andere oplossing om van die rottige filesort af te komen, of valt mijn doel misschien op een andere manier te bereiken?

[whoami]

Je wilt dus het record teruggeven met het hoogste id?

Heb je een index op ID liggen? Misschien haalt dat wat uit. (Ik weet niet of MySQL er rekening mee gaat houden).

Anders kan je natuurlijk ook je query in 2 stappen doen:
eerst:

code:

select max(id) from tabel where id <= 10597

en dan kan je met het resultaat van die query het gewenste record gaan ophalen.

Of, nog beter nu ik er aan denk:

code:

select max(id) , veld1, veld2, .... from tabel where id <= 10597
group by veld1, veld2, ...

Met deze query ga je het record gaan ophalen dat het hoogste id heeft (dat kleiner is dan 10597)

[ Voor 9% gewijzigd door whoami op 09-09-2003 15:04 ]

[marty]

whoami schreef op 09 September 2003 @ 15:03:
Je wilt dus het record teruggeven met het hoogste id?

Heb je een index op ID liggen? Misschien haalt dat wat uit. (Ik weet niet of MySQL er rekening mee gaat houden).

zoals ik al schreef is dat m'n primary key

Anders kan je natuurlijk ook je query in 2 stappen doen:
eerst:

code:

select max(id) from tabel where id <= 10597

en dan kan je met het resultaat van die query het gewenste record gaan ophalen.

Die max is opzich wel een aardig alternatief. Dan haal ik eerst het id op en daarna alle relevante data nog eens.... met die max duurt het 3.76 seconde. Nog niet echt geweldig, maar toch al een flinke afname

Of, nog beter nu ik er aan denk:

code:

select max(id) , veld1, veld2, .... from tabel where id <= 10597
group by veld1, veld2, ...

Met deze query ga je het record gaan ophalen dat het hoogste id heeft (dat kleiner is dan 10597)

eh...dan moet ik heeeeeeel veel group by's gaan definieren. Dat zal helemaal een potje traag worden.

Dit is m'n hele query namelijk:

code:

SELECT
    rbs.*,
    bc.*,
    rbd.omschrijving_bedrijf, rbd.bijzonderheden_bedrijf, rbd.vacature_site_monitoring, rbd.site_monitoring_wkn
FROM rbs, rbs_vestigingen
    LEFT JOIN bedrijven_contact AS bc ON rbs.cid = bc.u_contact_id
    LEFT JOIN rbs_base_data AS rbd ON rbs.stam_id = rbd.stam_id
WHERE rbs.rbs_code != 'X' AND rbs_vestigingen.rbs_id = rbs.id AND rbs.id <= 10597
ORDER BY id DESC LIMIT 0,1

Maar die suggestie met die MAX was toch erg nuttig.
Wil geen zeikerd zijn, maar weet iemand anders misschien een nog beter alternatief vind 3.76 seconde namelijk nog steeds best veel

(oh...krijg daar geheid vragen over natuurlijk, hier is de explain van die query:

code:

mysql> explain SELECT
    -> MAX(rbs.id)
    -> FROM rbs, rbs_vestigingen
    -> LEFT JOIN bedrijven_contact AS bc ON rbs.cid = bc.u_contact_id
    -> LEFT JOIN rbs_base_data AS rbd ON rbs.stam_id = rbd.stam_id
<= 10597RE rbs.rbs_code != 'X' AND rbs_vestigingen.rbs_id = rbs.id AND rbs.id
    -> ;
+-----------------+--------+---------------+---------+---------+------------------------+------+-------------+
| table           | type   | possible_keys | key     | key_len | ref                    | rows | Extra       |
+-----------------+--------+---------------+---------+---------+------------------------+------+-------------+
| rbs_vestigingen | index  | rbs_id        | rbs_id  |      11 | NULL                   | 7853 | Using index |
| rbs             | eq_ref | PRIMARY       | PRIMARY |       8 | rbs_vestigingen.rbs_id |    1 | where used  |
| bc              | eq_ref | PRIMARY       | PRIMARY |       8 | rbs.cid                |    1 | Using index |
| rbd             | ref    | stam_id       | stam_id |       9 | rbs.stam_id            |    1 | Using index |
+-----------------+--------+---------------+---------+---------+------------------------+------+-------------+
4 rows in set (0.00 sec)

[whoami]

Heb je indexen op de velden waarop je filtert? (In beide tabellen)?

Waarom doe je een SELECT * ? Heb je wel alle velden nodig?

[slm]

MySQL is zeker geen held in het gebruik van indices. In versie 4.x gaat dat alweer een stuk beter maar vaak kan je daar niet zelf voor kiezen.

Wat wel wil helpen is MySQL een handje helpen door het gebruik van een index af te dwingen:

SELECT * FROM blah USE INDEX (veld) WHERE veld < value ORDER BY veld DESC LIMIT 0, X

Grote kans dat je dan geen filesort meer gebruikt.

Let wel: geen NULL values in je index veld.

[ Voor 3% gewijzigd door slm op 09-09-2003 15:33 ]

[marty]

whoami schreef op 09 September 2003 @ 15:32:
Heb je indexen op de velden waarop je filtert? (In beide tabellen)?

op rbs_code stond geen index, maar dat is een enum veld waar maar 5 waardes in voor kunnen komen. dacht dus niet dat het veel zou uitmaken. heb het toch even gedaan en dat scheelt inderdaad maar 0.16 seconde. dus hou dan nog steeds 3.60 over

Waarom doe je een SELECT * ? Heb je wel alle velden nodig?

klopt. ik heb ze allemaal nodig. Het is voor een heel groot formulier waar je alle gegevens kunt aanpassen

slm schreef op 09 September 2003 @ 15:32:
MySQL is zeker geen held in het gebruik van indices. In versie 4.x gaat dat alweer een stuk beter maar vaak kan je daar niet zelf voor kiezen.

Wat wel wil helpen is MySQL een handje helpen door het gebruik van een index af te dwingen:

SELECT * FROM blah USE INDEX (veld) WHERE veld < value ORDER BY veld DESC LIMIT 0, X

maar als ik naar die explain kijk dan pakt ie alle goeie indices al....of denk jij daar anders over?

Grote kans dat je dan geen filesort meer gebruikt.

eeehhhh...zie eerste post. het probleem is dus dat ie altijd een filesort lijkt te gebruiken als je een order by desc doet.

[whoami]

Ik zie ook dat je 2x een LEFT JOIN gebruikt. Is het nodig dat je een LEFT JOIN doet? Is een INNER join niet voldoende?

[marty]

whoami schreef op 09 September 2003 @ 16:00:
Ik zie ook dat je 2x een LEFT JOIN gebruikt. Is het nodig dat je een LEFT JOIN doet? Is een INNER join niet voldoende?

Nopes, helaas niet. die twee tabellen die ik left-join daar staat niet altijd een overeenkomstig record in.

[whoami]

Heb je een index op rbs_code?
en kan je dan dit statement:

code:

WHERE rbs_code != 'X'

niet herschrijven ala:

code:

WHERE rbs_code IN ('A', 'B', 'C', ... )

?

[marty]

whoami schreef op 09 September 2003 @ 16:07:
Heb je een index op rbs_code?

marty schreef op 09 September 2003 @ 15:50:
[...]

op rbs_code stond geen index, maar dat is een enum veld waar maar 5 waardes in voor kunnen komen. dacht dus niet dat het veel zou uitmaken. heb het toch even gedaan en dat scheelt inderdaad maar 0.16 seconde. dus hou dan nog steeds 3.60 over

en kan je dan dit statement:

code:

WHERE rbs_code != 'X'

niet herschrijven ala:

code:

WHERE rbs_code IN ('A', 'B', 'C', ... )

ik heb gewoon heel die WHERE rbs_code != 'X' er even uitgegooid en dat gaf nauwelijks verschil: 3:43 seconde. Dus nog niet eens een verschil van 0.2 Dus herschrijven of anderzins er mee knoeien zal niet echt helpen.

Ik ontdekte trouwens nog wel iets anders raars:

code:

mysql> SELECT
    -> MAX(rbs.id)
    -> FROM rbs, rbs_vestigingen
    -> LEFT JOIN bedrijven_contact AS bc ON rbs.cid = bc.u_contact_id
    -> LEFT JOIN rbs_base_data AS rbd ON rbs.stam_id = rbd.stam_id
    -> WHERE rbs_vestigingen.rbs_id = rbs.id AND rbs.id <= 10597;
+-------------+
| MAX(rbs.id) |
+-------------+
|       10576 |
+-------------+
1 row in set (9.26 sec)
  
mysql> SELECT
    -> MAX(rbs.id)
    -> FROM rbs, rbs_vestigingen
    -> LEFT JOIN bedrijven_contact AS bc ON rbs.cid = bc.u_contact_id
    -> LEFT JOIN rbs_base_data AS rbd ON rbs.stam_id = rbd.stam_id
    -> WHERE rbs_vestigingen.rbs_id = rbs.id AND rbs.id <= 10597
    -> LIMIT 0,1;
+-------------+
| MAX(rbs.id) |
+-------------+
|       10576 |
+-------------+
1 row in set (3.43 sec)

Dus zodra ik die LIMIT 0,1 (die nergens voor nodig is en imo daar niet eens hoort te staan - vandaar ook dat ik 'm weghaalde) weghaal duurt de query ineens bijna 3x zo lang MySQL maakt het steeds bonter Dat klopt toch voor geen kant?
En het is ook geen tijdelijke hickup van de server, want heb dit een stuk of wat keren herhaalt

[whoami]

Voor die select max() query te doen, heb je imho toch die andere tabel niet nodig?
Die join kan je imho weglaten in die query.

[marty]

whoami schreef op 09 September 2003 @ 16:20:
Voor die select max() query te doen, heb je imho toch die andere tabel niet nodig?
Die join kan je imho weglaten in die query.

thanx whoami! Natuurlijk!

[slm]

[b][message=18705005,noline]marty schreef op 09 September 2003 @
maar als ik naar die explain kijk dan pakt ie alle goeie indices al....of denk jij daar anders over?

Ik niet, MySQL soms wel... (ofwel: je kan het altijd proberen. Soms geeft ie bij Key de juiste index aan, maar zegt ie bij extra: 'where used; using filesort' ipv 'where used;using index')

eeehhhh...zie eerste post. het probleem is dus dat ie altijd een filesort lijkt te gebruiken als je een order by desc doet.

Orde by desc geeft niet zonder meer een filesort. Meestal gebeurt het:
1. als hij geen index kan 'vinden' of automatisch gebruikt
2. in combinatie met where veld <|<=|>|>= x én order by desc

Situatie 2 is enigszins logisch. MySQL moet namelijk:
1. ervoor zorgen dat de tabel omgekeerd moet worden gesorteerd (helaas bestaan er geen desc-indices)
2. daar een filtering in maken welke aan je voorwaarde voldoet

/EDIT
Btw, welke versie van MySQL gebruik je en wat is de explain van die 2 queries die hierboven staan? (ivm met die LIMIT delay)

[ Voor 10% gewijzigd door slm op 09-09-2003 17:38 ]

[marty]

slm schreef op 09 September 2003 @ 17:28:
Orde by desc geeft niet zonder meer een filesort. Meestal gebeurt het:
1. als hij geen index kan 'vinden' of automatisch gebruikt
2. in combinatie met where veld <|<=|>|>= x én order by desc

Situatie 2 is enigszins logisch. MySQL moet namelijk:
1. ervoor zorgen dat de tabel omgekeerd moet worden gesorteerd (helaas bestaan er geen desc-indices)
2. daar een filtering in maken welke aan je voorwaarde voldoet

maw, daar valt niets aan te doen: learn to live with it ..?

Btw, welke versie van MySQL gebruik je en wat is de explain van die 2 queries die hierboven staan? (ivm met die LIMIT delay)

code:

mysql> explain SELECT
    -> MAX(rbs.id)
    -> FROM rbs, rbs_vestigingen
    -> LEFT JOIN bedrijven_contact AS bc ON rbs.cid = bc.u_contact_id
    -> LEFT JOIN rbs_base_data AS rbd ON rbs.stam_id = rbd.stam_id
    -> WHERE rbs_vestigingen.rbs_id = rbs.id AND rbs.id <= 10597;
+-----------------+--------+---------------+---------+---------+------------------------+------+-------------+
| table           | type   | possible_keys | key     | key_len | ref                    | rows | Extra       |
+-----------------+--------+---------------+---------+---------+------------------------+------+-------------+
| rbs_vestigingen | index  | rbs_id        | rbs_id  |      11 | NULL                   | 7853 | Using index |
| rbs             | eq_ref | PRIMARY       | PRIMARY |       8 | rbs_vestigingen.rbs_id |    1 | where used  |
| bc              | eq_ref | PRIMARY       | PRIMARY |       8 | rbs.cid                |    1 | Using index |
| rbd             | ref    | stam_id       | stam_id |       9 | rbs.stam_id            |    1 | Using index |
+-----------------+--------+---------------+---------+---------+------------------------+------+-------------+
4 rows in set (0.00 sec)
 
mysql> explain SELECT
    -> MAX(rbs.id)
    -> FROM rbs, rbs_vestigingen
    -> LEFT JOIN bedrijven_contact AS bc ON rbs.cid = bc.u_contact_id
    -> LEFT JOIN rbs_base_data AS rbd ON rbs.stam_id = rbd.stam_id
    -> WHERE rbs_vestigingen.rbs_id = rbs.id AND rbs.id <= 10597
    -> LIMIT 0,1;
+-----------------+--------+---------------+---------+---------+------------------------+------+-------------+
| table           | type   | possible_keys | key     | key_len | ref                    | rows | Extra       |
+-----------------+--------+---------------+---------+---------+------------------------+------+-------------+
| rbs_vestigingen | index  | rbs_id        | rbs_id  |      11 | NULL                   | 7853 | Using index |
| rbs             | eq_ref | PRIMARY       | PRIMARY |       8 | rbs_vestigingen.rbs_id |    1 | where used  |
| bc              | eq_ref | PRIMARY       | PRIMARY |       8 | rbs.cid                |    1 | Using index |
| rbd             | ref    | stam_id       | stam_id |       9 | rbs.stam_id            |    1 | Using index |
+-----------------+--------+---------------+---------+---------+------------------------+------+-------------+
4 rows in set (0.00 sec)
 
mysql> select version();
+-----------+
| version() |
+-----------+
| 3.23.41   |
+-----------+
1 row in set (0.00 sec)

2x exact dezelfde explain dus...
echt vaag....
Als ik die JOINs weghaal dan krijg je wel het verwachte resultaat (0.44 met LIMIT en 0.38 zonder LIMIT. Kennelijk doet mysql dus iets heel erg vaags met die JOINS

[slm]

Volgens mij heb je een klein bugje te pakken.

Ben het nooit zelf tegengekomen en met google zo direct ook niets wijzer geworden. Kennelijk is de optimizer ergens de weg kwijt geraakt in je query...

NB. Je hebt trouwens nog steeds die 2 overbodige tabellen in je join niet weggehaald, maar ik neem aan dat dat met die LIMIT bug niets uitmaakt?

[marty]

slm schreef op 09 september 2003 @ 20:28:
NB. Je hebt trouwens nog steeds die 2 overbodige tabellen in je join niet weggehaald, maar ik neem aan dat dat met die LIMIT bug niets uitmaakt?

marty schreef op 09 september 2003 @ 18:25:
Als ik die JOINs weghaal dan krijg je wel het verwachte resultaat (0.44 met LIMIT en 0.38 zonder LIMIT. Kennelijk doet mysql dus iets heel erg vaags met die JOINS

ik zal eens kijken of ik 'm met andere tabellen kan repliceren

[bigtree]

slm schreef op 09 september 2003 @ 17:28:
[...]

Orde by desc geeft niet zonder meer een filesort. Meestal gebeurt het:
1. als hij geen index kan 'vinden' of automatisch gebruikt
2. in combinatie met where veld <|<=|>|>= x én order by desc

In principe gebruikt MySQL zelfs dan nog een buffer. Pas als die vol is gaat hij een filesort gebruiken.

In the cases where MySQL have to sort the result, it uses the following algorithm:

- Read all rows according to key or by table scanning. Rows that don't match the WHERE clause are skipped.
- Store the sort-key in a buffer (of size sort_buffer).
- When the buffer gets full, run a qsort on it and store the result in a temporary file. [...]

(Uit de manual).

[slm]

Is leuk, maar de praktijk (zeker in versie 3.x) wijst anders uit.

Zelfs met een tabel van zeg 5 records en 4 velden, gebruikt hij met <|<=|>|>= x en order by desc nog een filesort. Buffer of geen buffer.