[MySQL] Left join op twee inner selects gaat langzaam

[Harrie_]

Min of meer wat de titel aangeeft; hier wat versimpelde code:

SQL:

SELECT
    *
FROM mastertable
LEFT JOIN (
    SELECT
          id
        , name
        , created_at
    FROM table
    -- hier nog een aantal joins
    WHERE name = 'a'
    ORDER BY created_at DESC
) AS table1
  ON mastertable.created_at = table1.created_at
LEFT JOIN (
    SELECT
          id
        , some_other_name
        , created_at
    FROM table
    -- hier nog een aantal joins
    WHERE some_other_name = 'b'
    ORDER BY created_at DESC
) AS table2
  ON mastertable.created_at = table2.created_at

Als ik de subqueries 'table1' en 'table2' afzonderlijk run dan krijg ik in 0.01x seconde resultaat, als ik de query als totaal run dan duurt het enkele seconden. Beide subqueries retourneren ongeveer 200 rows.

Wat ik al geprobeerd heb:

• Googelen ik krijg niet heel veel relevante resultaten, één stackoverflow-result had het over "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" (de MySQL versie van "WITH NO LOCK"), als ik dit toepas duurt de complete query 2 keer zo lang.
• Ik heb geprobeerd de query om te bouwen maar vanwege de complexiteit van de query (de echte query is natuurlijk vele malen uitgebreider) krijg ik dit niet goed voor elkaar. Omdat ik meerdere subsets uit 1 table wil joinen kan ik eigenljik ook niet om subqueries heen. Ik heb geprobeerd om complete sets te joinen en alle WHERE-statements pas aan het einde toe te passen maar dit levert (te)veel werk op omdat de query vanuit code wordt gegenereerd, afhankelijk van allerlei settings en selecties kunnen er meerdere subsets gejoind worden.
• Indexes toepassen, volgens mij haalt dit niet veel uit omdat ik twee resultaten join die in principe uit een temp-table komen waarop dus geen index van toepassing is (denk ik?)
• Toevoegen van een ORDER BY bij beide subsqueries zodat deze aan het eind van de rit sneller/makkelijker gejoind kunnen worden. Dit lijkt niets te doen.

Wie heeft het verlossende antwoord of kan in me in de juiste richting dirigeren?

[Eegee]

Misschien begrijp ik 'm verkeerd en m'n SQL is wat roestig, maar ik zou een UNION verwachten tussen table1 en table2 (want voor beide haal je dezelfde velden op, dus die tabellen bevatten equivalente data?) en daarna pas dat geheel left joinen met mastertable.

[fopjurist]

Je kunt "ORDER BY created_at DESC" weglaten en deze index gebruiken:

code:

ALTER TABLE table ADD INDEX (created_at, name);

Als dat niet werkt, kun je dan de output delen van:

code:

SHOW CREATE TABLE mastertable;
SHOW CREATE TABLE table;
EXPLAIN jouw query;

[Harrie_]

Eegee schreef op dinsdag 5 december 2023 @ 14:29:
Misschien begrijp ik 'm verkeerd en m'n SQL is wat roestig, maar ik zou een UNION verwachten tussen table1 en table2 (want voor beide haal je dezelfde velden op, dus die tabellen bevatten equivalente data?) en daarna pas dat geheel left joinen met mastertable.

Goed gezien. Inmiddels heb ik de pseudo-query aangepast want ik haal dus niet dezelfde velden op (een aantal velden wel om op te joinen, maar voor iedere subquery een andere column met data).

[Harrie_]

fopjurist schreef op dinsdag 5 december 2023 @ 14:30:
Je kunt "ORDER BY created_at DESC" weglaten en deze index gebruiken:

code:

ALTER TABLE table ADD INDEX (created_at, name);

Die index zit er al op.

fopjurist schreef op dinsdag 5 december 2023 @ 14:30:
Als dat niet werkt, kun je dan de output delen van:

code:

SHOW CREATE TABLE mastertable;
SHOW CREATE TABLE table;
EXPLAIN jouw query;

Er worden in de daadwerkelijke query zóveel tabellen gejoind dat dit bijna ondoenlijk is (+ dat ik dan een hoop zaken moet gaan lopen 'anonimiseren'). Als ik 'm helemaal plat sla kom ik op bovenstaande pseudo-query uit en ik ben eigenlijk vooral op zoek naar een verklaring waarom de afzonderlijke queries supersnel resultaat opleveren maar dat het joinen van die resultaten voor vertraging zorgt.

[fopjurist]

Harrie_ schreef op dinsdag 5 december 2023 @ 14:45:
[...]

Er worden in de daadwerkelijke query zóveel tabellen gejoind dat dit bijna ondoenlijk is (+ dat ik dan een hoop zaken moet gaan lopen 'anonimiseren'). Als ik 'm helemaal plat sla kom ik op bovenstaande pseudo-query uit en ik ben eigenlijk vooral op zoek naar een verklaring waarom de afzonderlijke queries supersnel resultaat opleveren maar dat het joinen van die resultaten voor vertraging zorgt.

Om je te kunnen helpen moet ik zien hoe de query wordt uitgevoerd. Wat is het exacte datatype dat SHOW CREATE TABLE laat zien voor created_at in mastertable, voor name, some_other_name en created_at in table, en wat zijn de eerste twee rijen van EXPLAIN (voor de query zonder ORDER BY in de subquery)?

[OnTracK]

Harrie_ schreef op dinsdag 5 december 2023 @ 14:45:
[...]


Die index zit er al op.


[...]


Er worden in de daadwerkelijke query zóveel tabellen gejoind dat dit bijna ondoenlijk is (+ dat ik dan een hoop zaken moet gaan lopen 'anonimiseren'). Als ik 'm helemaal plat sla kom ik op bovenstaande pseudo-query uit en ik ben eigenlijk vooral op zoek naar een verklaring waarom de afzonderlijke queries supersnel resultaat opleveren maar dat het joinen van die resultaten voor vertraging zorgt.

al genoemd: haal die order by uit je subqueries weg. check of die index(en) daadwerkelijk gebruikt wordt via een explain. Een soort van simpele uitleg van indexen (in MySQL) is dat je maar één index per table kan gebruiken, en een order by clause zorgt dat je een index waarschijnlijk niet meer kunt gebruiken als filter/join.

Volgende mogelijkheid: is het een optie dit te herschrijven zonder subqueries?

[fopjurist]

OnTracK schreef op dinsdag 5 december 2023 @ 15:04:
[...]
Volgende mogelijkheid: is het een optie dit te herschrijven zonder subqueries?

Ook al genoemd

[OnTracK]

Alleen door de TS toch? Ik zie in het genoemde voorbeeld geen reden waarom dit geen join kan zijn. Daarvoor moet je de join clause wel in de join laten, niet in de where: Want dan bouw je effectief een inner join.

[Harrie_]

fopjurist schreef op dinsdag 5 december 2023 @ 14:57:
[...]

Om je te kunnen helpen moet ik zien hoe de query wordt uitgevoerd. Wat is het exacte datatype dat SHOW CREATE TABLE laat zien voor created_at in mastertable, voor name, some_other_name en created_at in table, en wat zijn de eerste twee rijen van EXPLAIN (voor de query zonder ORDER BY in de subquery)?

Het datatype van created_at is datetime (die ik in de WHERE-clause als DATE cast).
De complete explain ziet er zo uit:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived3>	ALL					28625	*1
1	PRIMARY	ggm	ALL					5	*2
1	PRIMARY	gm	ALL					5725	*2
1	PRIMARY	gi	const	PRIMARY	PRIMARY	4	const	1
1	PRIMARY	mdr	eq_ref	PRIMARY	PRIMARY	4	gi.measurement_id	1	*2
1	PRIMARY	ggm	ALL					5	*2
1	PRIMARY	gm	ALL					5725	*2
1	PRIMARY	gi	const	PRIMARY	PRIMARY	4	const	1
1	PRIMARY	mdr	eq_ref	PRIMARY	PRIMARY	4	gi.measurement_id	1	*2
3	DERIVED	ggm	ALL					5	*3
3	DERIVED	gm	index		search_index_createdat	16		5725	*4
3	DERIVED	gi	eq_ref	PRIMARY	PRIMARY	4	gm.index_id	1	*5

Extra	Description
*1	Using where; Using filesort
*2	Using where
*3	Using where; Using temporary
*4	Using where; Using index; Using join buffer (flat, BNL join)
*5	Using where; Distinct

[Oon]

Zie ik goed dat hij een filesort gaat doen terwijl je grootste derived table maar 28k rows heeft?
Hoeveel (of weinig) geheugen heeft MySQL? Als je die filesort kunt voorkomen zal het mogelijk al een stuk sneller gaan

[luukvr]

snap het gebruik van sub-queries niet helemaal, misschien ligt daar het probleem:

code:

SELECT mastertable.*, table1.id, table1.name, table2.id, table2.some_other_name
FROM mastertable
LEFT JOIN table table1 ON table1.name='a' AND table1.created_at=mastertable.created_at
LEFT JOIN table table2 ON table2.some_other_name='b' AND table2.created_at=mastertable.created_at

doet exact het zelfde (behalve dat er misschien nog spannende dingen in je 'hier nog een aantal joins' zitten

[BCC]

Je left joined nu 2x dezelfde tabel, toch? Dan kan het ook nog als OR

[Harrie_]

luukvr schreef op dinsdag 5 december 2023 @ 16:12:
snap het gebruik van sub-queries niet helemaal, misschien daar ligt het probleem:

code:

SELECT mastertable.*, table1.id, table1.name, table2.id, table2.some_other_name
FROM mastertable
LEFT JOIN table table1 ON table1.name='a' AND table1.created_at=mastertable.created_at
LEFT JOIN table table2 ON table2.some_other_name='b' AND table2.created_at=mastertable.created_at

doet exact het zelfde (behalve dat er misschien nog spannende dingen in je 'hier nog een aantal joins' zitten

Ik probeer zo goed en zo kwaad als het kan het probleem wat te versimpelen naar een 'simpele' query. De daadwerkelijke query is een paar honderd regels lang waarbij op basis van settings die op user-basis zijn ingesteld er bepaalde meetwaarden in de totale query worden opgenomen. Helaas is de tabel structuur niet zo simpel dat ik 1 tabel heb met entiteiten en 1 tabel met meetwaarden. In plaats daarvan heb ik een tabel met entiteiten, een tabel met subentiteiten, een tabel met subsubentiteiten, etc. meetwaarden kunnen absolute waarden zijn, maar kunnen ook berekende waarden zijn op basis van bepaalde settings. Uiteindelijk is het gewoon een complex geheel, dat gezegd hebbende; wat ik niet begrijp is dat ik 2 subqueries afzonderlijk kan afvuren en in enkele ms resultaat heb, maar op het moment dat ik deze subqueries aan elkaar join de totale query er ineens een aantal seconden over doet.

[Harrie_]

BCC schreef op dinsdag 5 december 2023 @ 16:18:
Je left joined nu 2x dezelfde tabel, toch? Dan kan het ook nog als OR

Ja behalve dat dezelfde table in de ene subquery ander joins heeft dan die tabel in de andere subquery

[BCC]

Harrie_ schreef op dinsdag 5 december 2023 @ 16:19:
[...]


wat ik niet begrijp is dat ik 2 subqueries afzonderlijk kan afvuren en in enkele ms resultaat heb, maar op het moment dat ik deze subqueries aan elkaar join de totale query er ineens een aantal seconden over doet.

Dan pakt Mysql OF het verkeerde query plan omdat je query zo ingewikkeld is OF je server heeft te weinig ram om beide joins tegelijk in het geheugen te doen.

Het laatste kun je zien als je EXPLAIN doet van snelle query 1, EXPLAIN van snelle query 2 en de explain van Langzame Query 3.

[ Voor 11% gewijzigd door BCC op 05-12-2023 16:24 ]

[krisyboy]

Ik probeer zo goed en zo kwaad als het kan het probleem wat te versimpelen naar een 'simpele' query. De daadwerkelijke query is een paar honderd regels lang waarbij op basis van settings die op user-basis zijn ingesteld er bepaalde meetwaarden in de totale query worden opgenomen. Helaas is de tabel structuur niet zo simpel dat ik 1 tabel heb met entiteiten en 1 tabel met meetwaarden. In plaats daarvan heb ik een tabel met entiteiten, een tabel met subentiteiten, een tabel met subsubentiteiten, etc. meetwaarden kunnen absolute waarden zijn, maar kunnen ook berekende waarden zijn op basis van bepaalde settings. Uiteindelijk is het gewoon een complex geheel, dat gezegd hebbende; wat ik niet begrijp is dat ik 2 subqueries afzonderlijk kan afvuren en in enkele ms resultaat heb, maar op het moment dat ik deze subqueries aan elkaar join de totale query er ineens een aantal seconden over doet.

Waarom de individuele query's sneller zijn komt omdat je daar simpel in bijvoorbeeld 100 rows zoekt. Wanneer je joint moet je voor deze 100 rows alle corresponderende data erbij zoeken. (bijvoorbeeld 100*100) hoe meer joins je doet hoe meer dit toeneemt waardoor de query uiteindelijk een stuk langzamer is.

Dat is geloof ik hoe het werkt, mocht het niet zo zijn be my guest en corrigeer het

[fopjurist]

Harrie_ schreef op dinsdag 5 december 2023 @ 15:58:
[...]


Het datatype van created_at is datetime (die ik in de WHERE-clause als DATE cast).

Dan heb je de OP toch wat teveel vereenvoudigd. Je ziet het in EXPLAIN misgaan omdat de koppeling van ggm op gm geen index gebruikt. Ik zou de datum als apart veld aan de tabel toevoegen (aan table, niet per se aan mastertable) en de index aanpassen naar (created_at_date, name). Het alternatief is om de joinconditie aan te passen naar table1.created_at >= DATE(mastertable.created_at) AND table1.created_at < DATE_ADD(DATE(mastertable.created_at), INTERVAL 1 DAY), maar dan kun je de index niet gebruiken voor de vergelijking op name (of je moet de name-kolom vooraan zetten in de index en de lengte van de index gelijk maken aan de maximale lengte van het datatype van name, maar dat is ook weer nadelig voor de snelheid).

De complete explain ziet er zo uit:

<derived3> wordt gesorteerd. In de query in de OP wordt niets gesorteerd. Heb je toch een sortering op mastertable?

Oon schreef op dinsdag 5 december 2023 @ 16:02:
Zie ik goed dat hij een filesort gaat doen terwijl je grootste derived table maar 28k rows heeft?
Hoeveel (of weinig) geheugen heeft MySQL? Als je die filesort kunt voorkomen zal het mogelijk al een stuk sneller gaan

Filesort betekent niet noodzakelijkerwijs dat er bestanden worden gebruikt.

[ Voor 3% gewijzigd door fopjurist op 05-12-2023 16:35 ]

[luukvr]

master table
id created
1 2023
2 2024
3 2025

table
id name created
1 a 2023
2 a 2023
...
201 b 2023
202 b 2023

result van 2 left joins
master id, a, b
1 1 201
1 2 201
1 3 201
..
1 200 201
1 1 202
1 2 202
...
per record mastertable alle combinaties tussen 200x left join a en 200x leftjoin b (40k records per mastertable record)
dit moet allemaal in geheugen gegooid worden
+ misschien nog inefficiente casting van datetimes naar dates

[ Voor 9% gewijzigd door luukvr op 05-12-2023 16:38 ]

[Harrie_]

krisyboy schreef op dinsdag 5 december 2023 @ 16:24:
[...]


Waarom de individuele query's sneller zijn komt omdat je daar simpel in bijvoorbeeld 100 rows zoekt. Wanneer je joint moet je voor deze 100 rows alle corresponderende data erbij zoeken. (bijvoorbeeld 100*100) hoe meer joins je doet hoe meer dit toeneemt waardoor de query uiteindelijk een stuk langzamer is.

Dat is geloof ik hoe het werkt, mocht het niet zo zijn be my guest en corrigeer het

Ik wil niet pretenderen dat ik een expert ben alles behalve dat. Maar volgens mij gaat dit in deze situatie niet op. Ik heb twee inner queries die individueel retesnel zijn en allebei ongeveer 200 rows returnen. Dataset 1 (met 200 rows) aan dataset 2 (met ook 200 rows) plakken op basis van 1 column die hetzelfde is daar zou SQL toch echt geen moeite mee moeten hebben lijkt me...

fopjurist schreef op dinsdag 5 december 2023 @ 16:29:
[...]
Dan heb je de OP toch wat teveel vereenvoudigd. Je ziet het in EXPLAIN misgaan omdat de koppeling van ggm op gm geen index gebruikt. Ik zou de datum als apart veld aan de tabel toevoegen. Het alternatief is om de joinconditie aan te passen naar table1.created_at >= DATE(mastertable.created_at) AND table1.created_at < DATE_ADD(DATE(mastertable.created_at), INTERVAL 1 DAY), maar dan kun je de index niet gebruiken voor de vergelijking op name (of je moet de name-kolom vooraan zetten in de index en de lengte van de index gelijk maken aan de maximale lengte van name, maar dat is ook weer nadelig voor de snelheid).

Wellicht heb ik die wat te veel vereenvoudigd. Misschien denk ik te 'lineair' en verwacht ik ten onrechte dat mastertable (10ms) + subquery 1 (20ms) + subquery2 (20ms) samen 50ms en een beetje voltooid zou moeten zijn. Ik denk ook dat @BCC weleens gelijk kan hebben dat de 'totale query' een andere/vreemd execution plan pakt. Ik ben nog niet zo heel lang bezig met MySQL, ik heb jaren met MS SQL Server gewerkt en als ik dan dit soort vreemde praktijken tegenkwam dan had ik in SSMS de optie om het execution plan te bekijken waarbij je bij iedere stap (iedere select, join, whatever) precies te zien kreeg hoeveel (mili)seconden deze specifieke actie in beslag nam; nu ben ik een beetje 'verloren' in het zoeken waar het probleem zit...

fopjurist schreef op dinsdag 5 december 2023 @ 16:29:
[...]
<derived3> wordt gesorteerd. In de query in de OP wordt niets gesorteerd. Heb je toch een sortering op mastertable?

Ik heb een sortering op mastertable maar niet op de subqueries. Als ik die sortering eraf haal dan staat deze niet meer in EXPLAIN, maar hij wordt er ook niet sneller van...

[fopjurist]

Harrie_ schreef op dinsdag 5 december 2023 @ 16:40:
[...]

nu ben ik een beetje 'verloren' in het zoeken waar het probleem zit...

Ik ben het met je eens dat het topic lastig te lezen is omdat de meesten geen kaas hebben gegeten van MySQL. Probeer een van de twee oplossingen eens die ik heb aangereikt.

Één van de oorzaken zit hier: een query met WHERE DATE(created_at) = .... kan geen gebruik maken van een index.

Wellicht heb ik die wat te veel vereenvoudigd.

Het had geholpen als je de tabellen die je in de OP noemt daadwerkelijk had aangemaakt om het probleem te reproduceren. Het maken van een minimal reproducible example is een belangrijke stap om het probleem beter te begrijpen.

[Harrie_]

Dank voor alle antwoorden met in het bijzonder @fopjurist. Ik ben gisterenavond nog even aan het testen geweest en uiteindelijk heb ik 'm in z'n volledigheid supersnel kunnen krijgen (totale query < 30ms) door een losse index op created_at en een andere column waarop geselecteerd wordt i.c.m. het wijzigen van de WHERE-clause. Ik cast created_at niet langer als DATE en heb dit vervangen door:

code:

-- wanneer date = 05-12-2023

WHERE DATE >= 2023-12-05 00:00:00
  AND DATE <  2023-12-06 00:00:00