[mysql] query optimaliseren

[BetuweKees]

hi,

ik heb een tijdendatabase waaruit ik een ranglijst creeer met behulp van een mysql query. ik ben echter niet erg te spreken over de snelheid van deze query. ben nu al een aantal dagen bezig uit te zoeken hoe ik een en ander zou kunnen optimaliseren; heb nu ong een halve seconde tijdswinst weten te boeken, maar vind de 2.5 sec die resteren voor de query op mijn test server nog steeds veel te lang.

om een en ander wat duidelijker te maken eerst even wat code:

code:

$query:
¯¯¯¯¯¯

SELECT 
  leden.geslacht, 
  CONCAT_WS(' ', leden.voornaam, leden.tussen, leden.achternaam) AS naam, 
  afstand.omschrijving AS afstand, 
  MIN(overzicht.tijd) AS tijd 
FROM 
  tijd_overzicht AS overzicht 
  LEFT JOIN tijd_verbinding AS verbinding ON verbinding.verbinding_id = overzicht.verbinding_id 
  LEFT JOIN leden AS leden ON leden.leden_id = overzicht.leden_id 
  INNER JOIN tijd_afstand AS afstand ON afstand.afstand_id = verbinding.afstand_id 
WHERE 
  verbinding.afstand_id IN (3,5,7,8) 
  AND overzicht.tijd <> 0 
  AND overzicht.opmerking_id NOT IN (3,4) 
  AND leden.geslacht <> 'onbekend' 
GROUP BY 
  verbinding.afstand_id, 
  overzicht.leden_id 
ORDER BY 
  leden.geslacht DESC, 
  overzicht.leden_id, 
  afstand.meters;


EXPLAIN $query:
¯¯¯¯¯¯¯¯¯¯¯¯¯¯
+------------+--------+-----------------------+---------+---------+-------------------------+-------+---------------------------------------------+
| table      | type   | possible_keys         | key     | key_len | ref                     | rows  | Extra                                       |
+------------+--------+-----------------------+---------+---------+-------------------------+-------+---------------------------------------------+
| overzicht  | ALL    | NULL                  | NULL    |    NULL | NULL                    | 18043 | where used; Using temporary; Using filesort |
| verbinding | eq_ref | PRIMARY,verbinding_id | PRIMARY |       2 | overzicht.verbinding_id |     1 | where used                                  |
| leden      | eq_ref | PRIMARY,leden_id      | PRIMARY |       2 | overzicht.leden_id      |     1 | where used                                  |
| afstand    | eq_ref | PRIMARY,afstand_id    | PRIMARY |       1 | verbinding.afstand_id   |     1 |                                             |
+------------+--------+-----------------------+---------+---------+-------------------------+-------+---------------------------------------------+
4 rows in set (0.06 sec)


$query LIMIT 10:
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
+----------+-------------------------+---------+--------+
| geslacht | naam                    | afstand | tijd   |
+----------+-------------------------+---------+--------+
| vrouw    | Persoon 1               | 500m    |  47.37 |
| vrouw    | Persoon 1               | 1.500m  | 141.52 |
| vrouw    | Persoon 1               | 5.000m  | 552.64 |
| vrouw    | Persoon 2               | 500m    |  51.41 |
| vrouw    | Persoon 2               | 1.500m  | 156.10 |
| vrouw    | Persoon 3               | 500m    |  45.90 |
| vrouw    | Persoon 3               | 1.500m  | 137.92 |
| vrouw    | Persoon 4               | 500m    |  55.80 |
| vrouw    | Persoon 4               | 1.500m  | 187.82 |
| vrouw    | Persoon 5               | 500m    |  48.62 |
+----------+-------------------------+---------+--------+
10 rows in set (2.42 sec)

doel is om op basis van deze data een mooie ranglijst te creeeren. iemand wordt echter alleen opgenomen in de ranglijst indien hij/zijn de 4 afstanden die in de query vermeld worden (3, 5, 7 en verreden heeft. zoals het nu is komt er dus veel zinloze data terug, die later met behulp van een php script opgeschoond wordt, zodanig dat enkel die personen over blijven die aan de bovestaanden voorwaarde voldoen.

op basis van de explain denk ik dat ik er van uit mag gaan dat het wel goed zit wat betreft het gebruik van indexes. naar mijn idee zijn er dan nog twee zaken die ik zou kunnen doen om een en ander nog beter te laten draaien.
1. zorgen dat er geen gebruik meer wordt gemaakt van temporary en filesort
2. zorgen dat de grote hoeveelheid 'onzinnige' output die ik krijg verminderd.


@1: Ik heb geen idee hoe dit aan te pakken. Van de docs begrijp ik dat de een veroorzaakt wordt door de group by en de ander door de order by. Daarnaast weet ik dat deze sorting methodes verantworodelijk zijn voor performace verlies. Ik kan echter niet vinden hoe dit te ontlopen. Is er een mooie (snelle) oplossing met mySQL of moet ik een en ander maar gewoon niet gesorteerd later terug komen en die zelf doen met behulp van een stukje php code?

@2: Een subquery lijkt me hier een optie voor, maar helaas gebruik ik geen MySQL 4.1 (hoster wil niet), dus dit is niet echt een optie.
Heb zelf geprobeerd een en ander aan te pakken met een paar SELF JOINs, maar dat slikte mysql niet. Een vriend stelde een oplossing met EQUI JOINs voor, maar die query duurde zolang dat ik op een gegeven moment mijn sql server maar gerest heb..
Iemand die hier ideeen voor heeft?

[H!GHGuY]

gebruik een HAVING-clause (als dit in mySQL kan natuurlijk...)

code:

WHERE 
  verbinding.afstand_id IN (3,5,7,8) 
  ...
GROUP BY 
  verbinding.afstand_id,

wordt:

code:

GROUP BY 
  verbinding.afstand_id, 
HAVING
  verbinding.astand_id IN (3,5,7,8)

dit zorgt er voor dat je niet de vele records moet overlopen in de WHERE, maar dit wordt op minder records gedaan in de HAVING. daarbij is ... IN (...) een vrij dure query afaik.

voor de rest:
- indexen
- bij de WHERE de voorwaarden sorteren volgens hoeveelheid rijen ze schrappen. (probeer zo snel mogelijk zoveel mogelijk rijen te schrappen)

[NMe]

BetuweKees schreef op dinsdag 25 januari 2005 @ 23:54:
@2: Een subquery lijkt me hier een optie voor, maar helaas gebruik ik geen MySQL 4.1 (hoster wil niet), dus dit is niet echt een optie.

Subqueries zijn voor zover ik weet sowieso bijna altijd trager dan joins. Ik heb even geen benchmark om dat te bevestigen, en ik ben er ook niet 100% zeker van, maar het is in ieder geval wat een leraar bij mij op school me wijs heeft gemaakt.

Heb zelf geprobeerd een en ander aan te pakken met een paar SELF JOINs, maar dat slikte mysql niet.

Selfjoins kunnen wel in MySQL, maar dan met een soort van omweg:

MySQL:

SELECT *
FROM   mijn_tabel AS t1
       mijn_tabel AS t2
WHERE  t1.dit = t2.dat

Je GROUP BY statement klopt volgens mij ook niet helemaal. Je moet groupen op alle velden uit de select list die géén aggregate functie zijn, dus in dit geval op geslacht en afstand (en volgens mij ook op naam, ik weet niet zeker of CONCAT_WS onder aggregate funties wordt gerekend).

[ Voor 16% gewijzigd door NMe op 26-01-2005 00:37 ]

[SuperRembo]

-NMe- schreef op woensdag 26 januari 2005 @ 00:34:
[...]

Je GROUP BY statement klopt volgens mij ook niet helemaal. Je moet groupen op alle velden uit de select list die géén aggregate functie zijn, dus in dit geval op geslacht en afstand (en volgens mij ook op naam, ik weet niet zeker of CONCAT_WS onder aggregate funties wordt gerekend).

"naam" moet ook in het group by statement. De query geeft dan niet meer de resultaten die je wil hebben, namelijk de snelste tijden per persoon in plaats van de snelste tijden per onderdeel.

[JJvG]

concat functie is vaak een zeer vertragende functie. sloop die er eens uit en kijk eens of dat sneller gaat. Eventueel kun je deze vervangen door Select voornaam & " " & tussen & " " & achternaam as naam.

Daarnaast is NOT IN veel trager dan 'kaal' IN, omdat het een vergelijking vraagt van alle records en niet stopt wanneer een goed resultaat wordt gevonden

Group by en order by zijn niet echt vertragend, maar subquerys zou in dit geval wel prettiger zijn. Jammer dat dat niet mag

2,5 seconden aanmerken als traag een relatief begrip, omdat je niet vermeld over hoeveel records het gaat (10tallen, 1000en of milioenen) Dat het in jouw geval traag is snap ik, maar ben benieuwd over hoeveel records het gaat

[Brakkie]

Group by in combinatie met een order by kan juist wel vertragend zijn. Als de group by een andere listing geeft dan de order by is er namelijk een temporary table nodig.

mysql.com

Using temporary

To resolve the query, MySQL will need to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.

Een concat is juist wel een effectieve manier om een bepaald resultaat in een bepaald formaat te verkrijgen. Het is gewoon een simpele string functie die voor zover ik weet geen invloed heeft op de effectiviteit van een query an sich. Het aantal velden in je result set wordt er juist kleiner door,

[BetuweKees]

HIGHGuY schreef op woensdag 26 januari 2005 @ 00:29:
gebruik een HAVING-clause (als dit in mySQL kan natuurlijk...)

ik betwijfel of dit echt werkt; precies in dit deel van het where statement wordt juist de grootste selectie gemaakt, de andere drie voorwaarden zullen maar voor een paar records tellen, waardoor het gros van de data alnog door HAVING moet worden bewerkt. maar ik ga het ieder geval even proberen.

-NMe- schreef op woensdag 26 januari 2005 @ 00:34:
Je GROUP BY statement klopt volgens mij ook niet helemaal. Je moet groupen op alle velden uit de select list die géén aggregate functie zijn, dus in dit geval op geslacht en afstand (en volgens mij ook op naam, ik weet niet zeker of CONCAT_WS onder aggregate funties wordt gerekend).

ok dat zal ik even aanpassen

JJvG schreef op woensdag 26 januari 2005 @ 10:11:
concat functie is vaak een zeer vertragende functie. sloop die er eens uit en kijk eens of dat sneller gaat. Eventueel kun je deze vervangen door Select voornaam & " " & tussen & " " & achternaam as naam.

had zelf al gekeken wat er zou gebeuren als ik de select zonder concat deed, maar kon niet echt performance verschil merken..

Daarnaast is NOT IN veel trager dan 'kaal' IN, omdat het een vergelijking vraagt van alle records en niet stopt wanneer een goed resultaat wordt gevonden

goed punt.. die ga ik dan even andersom doen dus..

2,5 seconden aanmerken als traag een relatief begrip, omdat je niet vermeld over hoeveel records het gaat (10tallen, 1000en of milioenen) Dat het in jouw geval traag is snap ik, maar ben benieuwd over hoeveel records het gaat

heb je een punt
overzicht bestaat uit 18k records, verbinding net geen 4k, leden rond de 650 en afstand is klein (stuk of 10 ofzo)

bedankt voor de tips vast, ik ga eens kijken wat er gebeurt als ik een en ander aanpas..