MySQL query te traag

[Tsjilp]

Ik heb een query welke te traag draait (4 - 8 seconden).

Even kort wat de query doet:
Ik heb de volgende tabellen:
calendarnew: bevat alle events
Kolommen: calendarId, label, recur, until, enabled

calendareventsnew: bevat alle datums van de events (een event kan meerdere datums hebben)
Kolommen: eventId, calendarId, starttime, endtime

gm_markers: bevat alle locaties
Kolommen: pageId, lat, lng, label

Ik wil nu een lijst van alle events hebben, waarbij de kolom 'starttime' de datum geeft van de eerst volgende event, als er geen events in de toekomst zijn, geef dan de laatste datum. Met andere woorden: geeft de datum die het dichts bij 'nu' ligt

De query:

code:

SELECT 
SQL_CALC_FOUND_ROWS 
cn.*, gmm.label as `location`, 
UNIX_TIMESTAMP(cn.modificationdate) as `modificationdate`,
UNIX_TIMESTAMP(cn.creationdate) as `creationdate`, 
UNIX_TIMESTAMP(cn.until) as `until`,
UNIX_TIMESTAMP(cee.starttime) as `starttime`, 
UNIX_TIMESTAMP(cee.endtime)  as `endtime`,
FROM `calendarnew` cn   
LEFT JOIN `gm_markers` gmm ON cn.locationId = gmm.pageId 
LEFT JOIN calendareventsnew cee ON cee.eventId=IFNULL((SELECT eventId
FROM calendareventsnew  
WHERE `calendarId`=cn.calendarId AND starttime > NOW() 
ORDER BY abs(NOW() - starttime) LIMIT 1),   
(SELECT eventId
FROM calendareventsnew 
WHERE `calendarId`=cn.calendarId
ORDER BY abs(NOW() - starttime) LIMIT 1)) 
GROUP BY calendarId  
ORDER BY enabled ASC
LIMIT 0,80

Ik heb indexen staan op:
calendareventsnew: eventId, calendarId,[calendarId, starttime], starttime
calendarnew: calendarId, locationId, enabled

Iemand een idee hoe ik dit beter kan oplossen? Volgens mij heb ik de indexen al op de juiste plaats staan.

[Gtoniser]

En wat zegt mysql als je er een EXPLAIN SELECT van maakt ipv SELECT?

[NMe]

Je doet volgens mij veel te ingewikkeld. Als je nou eens een union maakt tussen een select van de eerste rij waarvan de datum groter is dan "nu" en een select van de hoogste datum die kleiner is dan "nu" en uit die union het eerste record bekijkt dan ben je er toch?

[Retpics]

Je ordered nu al je events op basis van ABS(NOW() - starttime). Ik denk dat je beter kunt joinen op een subquery waarin je de MIN(starttime) ophaalt die groter is dan NOW() en dan een tweede join op de daadwerkelijke rij die daarmee overeenkomt:

Zoiets:

code:

SELECT
...
LEFT JOIN (
  SELECT MIN(starttime) AS starttime FROM calendareventsnew WHERE starttime > NOW()
) AS firstEvent
LEFT JOIN calendareventsnew AS cee ON cee.starttime = firstEvent.starttime
...

Edit: Als je enkel de eerstvolgende datum nodig hebt dan heb je de tweede JOIN niet eens nodig, want dat is het resultaat van de eerste query.

[ Voor 14% gewijzigd door Retpics op 03-06-2014 15:38 ]

[Tribits]

Heb je die SQL_CALC_FOUND_ROWS echt nodig? Voor zover ik begrijp kan die ook nog wel eens voor een flinke verslechtering van de performance zorgen.

[Tsjilp]

Die SQL_CALC_FOUND_ROWS gebruik ik om te bepalen of er nog meer rijen zijn (voor paging).

Met de hint van NMe heb ik nu dit (uitgekleed):

code:

SELECT label, MAX(starttime) as 'starttime'
FROM calendarnew cn 

LEFT JOIN 
    ((SELECT calendarId, MIN(starttime) as starttime 
        FROM calendareventsnew 
        WHERE starttime>NOW() 
        GROUP BY calendarId) 
    UNION 
    (SELECT calendarId, MAX(starttime) as starttime 
        FROM calendareventsnew 
        WHERE starttime<NOW() 
        GROUP BY calendarId)) AS cee ON cee.calendarId = cn.calendarId 

WHERE starttime > '2014-04-01'

GROUP BY cn.calendarId

Ik kwam erachter dat UNIX_TIMESTAMP ook nogal invloed heeft op de performance...

Dit lijkt goed te gaan, maar zonder de WHERE starttime > '2014-04-01' is hij nog steeds traag. (+4 sec, met duurt hij 0,2 sec)

[ Voor 2% gewijzigd door Tsjilp op 04-06-2014 16:18 . Reden: query leesbaarder gemaakt ]

[TheNephilim]

Laat ook even de EXPLAIN SELECT zien zoals Gtoniser in "MySQL query te traag" al aangeeft.

[Tsjilp]

code:

mysql> EXPLAIN SELECT  SQL_CALC_FOUND_ROWS  cn.*, gmm.label as `location`,  UNIX_TIMESTAMP(cn.modificationdate) as `modificationdate`, UNIX_TIMESTAMP(cn.creationdate) as `creationdate`,  UNIX_TIMESTAMP(cn.until) as `until`, UNIX_TIMESTAMP(cee.starttime) as `starttime`,  UNIX_TIMESTAMP(cee.endtime)  as `endtime` FROM `calendarnew` cn      LEFT JOIN `gm_markers` gmm ON cn.locationId = gmm.pageId  LEFT JOIN calendareventsnew cee ON cee.eventId=IFNULL((SELECT eventId FROM calendareventsnew   WHERE `calendarId`=cn.calendarId AND starttime > NOW()  ORDER BY abs(NOW() - starttime) LIMIT 1),      (SELECT eventId FROM calendareventsnew  WHERE `calendarId`=cn.calendarId ORDER BY abs(NOW() - starttime) LIMIT 1))  GROUP BY calendarId   ORDER BY enabled ASC LIMIT 0,80;
+----+--------------------+-------------------+--------+----------------------------------------------+-------------+---------+-------------------------------+------+---------------------------------+
| id | select_type        | table             | type   | possible_keys                                | key         | key_len | ref                           | rows | Extra                           |
+----+--------------------+-------------------+--------+----------------------------------------------+-------------+---------+-------------------------------+------+---------------------------------+
|  1 | PRIMARY            | cn                | ALL    | NULL                                         | NULL        | NULL    | NULL                          | 8823 | Using temporary; Using filesort |
|  1 | PRIMARY            | gmm               | ref    | pageId                                       | pageId      | 5       | uitloper_nu_gro.cn.locationId |   11 |                                 |
|  1 | PRIMARY            | cee               | eq_ref | PRIMARY                                      | PRIMARY     | 4       | func                          |    1 |                                 |
|  3 | DEPENDENT SUBQUERY | calendareventsnew | ref    | calendarId,calendarId2                       | calendarId2 | 4       | uitloper_nu_gro.cn.calendarId |    5 | Using where; Using filesort     |
|  2 | DEPENDENT SUBQUERY | calendareventsnew | ref    | calendarId,calendarId2,starttime,starttime_2 | calendarId2 | 4       | uitloper_nu_gro.cn.calendarId |    5 | Using where; Using filesort     |
+----+--------------------+-------------------+--------+----------------------------------------------+-------------+---------+-------------------------------+------+---------------------------------+

[NMe]

Waarom heb je die union in een subquery gezet? Uit de eerste query in je union kan potentieel géén record terugkomen. Komt dat wel terug, dan wil je die hebben. Komt die niet terug, dan wil je dat de volgende query je data bevat. Je hebt die subquery dus niet nodig, je moet gewoon in je code het eerste record uit het resultaat halen. Daarmee heb je in elk geval de laatste twee filesorts in je explain weggewerkt, al zal dat het probleem niet zijn. Je probleem zit in de temporary table en filesort op calendarnew.

Ik heb wel een vermoeden waar het aan zou kunnen liggen. Kun je die WHERE starttime > '2014-04-01' eens vervangen door WHERE starttime IS NOT NULL?

[Tsjilp]

De

code:

IS NOT NULL

toevoegen helpt idd,
ik begrijp niet helemaal wat je met de UNION in de subquery bedoeld. Hoe zou jij dat oplossen dan?

[Tsjilp]

@NME: hoe bedoel je zonder subquery?

[NMe]

SQL:

SELECT label, MIN(starttime) as starttime 
FROM calendareventsnew 
WHERE starttime>NOW() 
GROUP BY calendarId) 

UNION 

SELECT label, MAX(starttime) as starttime 
FROM calendareventsnew 
WHERE starttime<NOW() 
GROUP BY calendarId

Daar gewoon de eerste rij uit selecteren (en de tweede negeren, mochten er twee zijn) levert precies hetzelfde op wat je nu ook hebt.

Waarom die WHERE trouwens effect had: ik vermoed dat je met die WHERE specifiek aangeeft welke index er voor die tabel gebruikt moet worden. MySQL kan als ik het me goed herinner per gejoinde tabel maar één index gebruiken. Ik denk dat er geen probleem meer is als je de query uit deze post gebruikt.

[Tsjilp]

O.a. het label staat niet in de tabel calendareventsnew, vandaar dat ik het als subquery doe. Linksom of rechtsom zal ik calendareventsnew en calendarnew moeten joinen, en welke volgorde ik dat doe maakt volgens mij niet zoveel uit.

[MaxxMark]

Ik heb helaas geen tijd om nu de gehele query uit te werken, maar ik vroeg me 4 dingen af:
1. De waarom van de tijd is iig logisch als je naar je explain kijkt. Het eerste deel laat hem gaan over 8000 regels (full table scan wat logisch is omdat je geen where gebruikt). Vervolgens joined ie per row op 11,1,5,5 regels ofwel 8000*11*1*5*5 regels warah ij over moet zoeken. Dat duurt lang

2. Is calc_found_rows() *echt* nodig. Deze zorgt er voor dat hij inderdaad al die regels af moet. Als er correct gebruik gemaakt wordt van indexen zal je limit 0,80 er voor zorgen dat hij niet de hele tabel door hoeft. Gaat het alleen om je pagination, doe dan een 0,81 en strip het laatste item. Indien count(x) == limit+1, dan weet je dat er een next page is.

3. Waarom wil je kosten wat het kost alles in 1 query doen? Waarom splitst je het niet op in 2 queries die vervolgens individueel sneller zijn dan samen genomen?

simpel voorbeeld:
select * from foo where (col1='foo' and col2='bar') or (col1='aa' and col2='bb');
uitgaande dat je netjes een gecombineerde sleutel op col1, col2 hebt kan de query optimizer hier niets mee een zal hij een full table scan gaan doen. Het opsplitsen in 2 queries:
select * from foo where (col1='foo' and col2='bar');
select * from foo where (col1='aa' and col2='bb');
is vele malen sneller.
In jouw voorbeeld zou een union mogelijk zijn, maar zover ik nu zie zou het prima in je businesslogic afgehandeld kunne nworden

4. Ik snapte je uitleg van je indexen niet helemaal

calendareventsnew: eventId, calendarId,[calendarId, starttime], starttime
calendarnew: calendarId, locationId, enabled

Als ik het goed opmaak heb je hier een index op calenderId en daarnaast op (calenderId, starttime) en daarnaast een op start time. De 1e calendarId is overbodig, daar deze al als eerste in (calendarId,starttime) zit.

[Tsjilp]

1. Klinkt logisch. Maar die hadden we al afgevangen door een where != null toe te voegen
2. Slimme oplossing, moet voor dit scenario afdoende zijn, al zal er dan in de business logic ook e.e.a aangepast moeten worden
3. Kosten wat het kost is een groot woord. Vind het alleen meestal wat 'netter'. Zal eens kijken wat de implementatie van jouw voorbeeld doet voor de performance
4. Klopt, een key op calendarId en een key op calendarId & starttime. Wist niet dat die eerste dan overbodig was.

Tnx in advance

[MaxxMark]

Reagerend nog op 1+3 gecombineerd:
Gevoelstechnisch heb je 2 situaties:
1 voor alle events vanaf nu
1 situatie voor als er geen evenement na nu is (namelijk 'het dichstbijzijnde evenement in het verleden)

Die 2 zou ik persoonlijk in 2 losse queries zetten omdat ik index technisch geen manier zie om dat eenvoudig te bepalen.

Voor de items vanaf nu kan je dan start_time > now() doen
en voor het eerste item in het verleden wher start_time < now order by start_time desc limit 1


En mbt 4: Gecombineerde sleutels werken altijd van links naar rechts (technisch best logisch, maar daar moet je maar even voor door de docs bladeren). Ofwel met een gecombineerde sleutel:

index (col1, col2, col3, col4)

kan je de volgende queries doen:
select * from foo where col1='bar';
select * from foo where col1='bar' and col2='foo'
... col1='bar' and col2='foo' and col3='x'
... col1='bar' and col2='foo' and col3='x' and col4='y'

Voor "order by" geldt hetzelfde. Zolang een index (hoeft niet dezelfde als voor de where te zijn) van links naar rechts matcht kan hij gebruikt worden

in alle bovenstaande situaties kan een order by gegeven worden op 1 individuele of gecombineerde sleutels van links naar rechts. Ofwel:
order by col1;
order by col1, col2;
you get the drift

Je kunt niet(!) wisselen in die volgorde. In dat geval moet je een andere sleutel hebben. In bovenstaande index kan je dus niet doen:

where col1='foo' and col4='bar';
of mixen met een andere index.

Mysql gebruikt altijd 1 index en pakt de index die de minste uiteindelijke resultaten geeft.

[NMe]

Tsjilp schreef op dinsdag 10 juni 2014 @ 10:03:
Linksom of rechtsom zal ik calendareventsnew en calendarnew moeten joinen, en welke volgorde ik dat doe maakt volgens mij niet zoveel uit.

Doe die join dan in de union, dus twee keer. Joinen op een indexeerbare fysieke tabel gaat sneller dan joinen op een tijdelijke tabel die je in je query impliciet maakt.