[MySQL] Query loeitraag, wat doe ik verkeerd?

[triet]

(My)SQL is niet mijn sterkste kant blijkt. Ik kan me er aardig mee redden en Queries die ik maak waarmee ik iets wil bereiken zijn vreselijk traag of geven mij niet de juiste informatie terug. Wellicht dat jullie mij hier een beetje mee op weg kunnen helpen zodat ik weer wat verder kan.

Situatie:

Op een bepaald moment is er een "event". Dit event heeft een bepaalde omschrijving (bijv. "Feestje"). Deze wordt opgeslagen in de event_tabel. Daarnaast zijn er mensen betrokken bij dit feestje. De id's daarvan worden opgeslagen in een koppeltabel (betrokkenen_tabel). Er kunnen uiteraard meerdere mensen op dit event zijn. Als laatste is er een tabel met de betrokkenen en hun naam.

code:

  events_tabel:
     id - MEDIUMINT
     event_datumtijd - DATETIME
     event_omschrijving - tinytext

  betrokkenen_tabel:
     events_tabel_id = MEDIUMINT (-> events_tabel.id)
     persoon_tabel_id - MEDIUMINT (-> persoon_tabel.id)

  persoon_tabel:
     id - MEDIUMINT
     naam - tinytext

  zoektabel:
     id - MEDIUMINT
     persoon_tabel_id - MEDIUMINT (-> persoon_tabel.id)

  Vulling events_tabel  
     1, 2013-12-21 10:11:12, 'Test'
     2, 2013-12-22 18:00:00, 'Test 2'
     3, 2013-12-23 19:00:00, 'Test 3'
     4, 2013-12-24 12:13:14, 'Test 4'

  Vulling betrokkenen_tabel:
     1, 100
     1, 101
     2, 101
     2, 109
     3, 109
     4, 102
     4, 103
     4, 109
     4, 110

  Vulling persoon_tabel:
     100, 'Ik'
     101, 'Jij'
     102, 'Hij'
     103, 'Zij'
     109, 'Wij'
     110, 'Jullie'

  Vulling zoektabel:
     1, 100
     1, 101

Wat makkelijk te bereiken is is te zoeken bij welke events bijv. persoon nr 103 betrokken is.

Maar wat ik wil is als volgt: ik wil op basis van een combinatie van aanwezige personen (opgeslagen in zoektabel met uniek id) een lijst krijgen van de events waar zij aanwezig zijn PLUS alle andere mensen die ook op die events aanwezig zijn. Dit gesorteerd op nieuwste events eerst. In de praktijk bevatten de tabellen miljoenen records waarbij voor elk event er meerdere mensen aanwezig zijn. Het aantal unieke mensen is ca. 100.000.

Oftewel in bovenstaand voorbeeld: zoektabel(1) = 101,103 dus alle events waar personen 101 en 103 geweest zijn incl. alle bezoekers.
Dus resulaat van deze query moet zijn:

code:

  event_tabel.id, event_tabel.datumtijd, persoon_tabel.id, persoon_tabel.naam
  
  2, 2013-12-22 18:00:00, 'Test 2', 101, 'Jij'        (want deze persoon was gequeried vanuit zoektabel)
  2, 2013-12-22 18:00:00, 'Test 2', 109, 'Wij'      (want deze persoon was ook aanwezig maar niet in zoektabel!)
  1, 2013-12-21 10:11:12, 'Test', 101, 'Jij'       (omdat deze persoon gequeried was vanuit zoektabel)
  1, 2013-12-21 10:11:12, 'Test', 100, 'Ik'       (deze was ook gequeried vanuit zoektabel)

Het is me gelukt om de lijst van events te krijgen maar niet ook nog'ns met alle andere mensen die ook op die events zijn.

Ik had eerst iets als:

code:

SELECT
  events_tabel.event_datumtijd
FROM events_tabel
LEFT JOIN betrokkenen_tabel ON events_tabel.id = betrokkenen_tabel.events_tabel.id
LEFT JOIN persoon_tabel ON persoon_tabel.id = betrokkenen_tabel.persoon_tabel_id
WHERE
  betrokkenen_tabel.persoon_tabel_id IN ( SELECT persoon_tabel_id FROM zoektabel WHERE id = 1 )
ORDER BY events_tabel.event_datumtijd DESC

Dat werkt maar LOEITRAAG. Ik hoop dat het voorbeeld een beetje duidelijk is. Wat doe ik fout of wat kan beter? Wellicht zelfs de hele structuur overhoop gooien? Ik wil dat deze z.s.m. reageert, maar zeker binnen 1 seconde.

Overigens zijn overal indexes op aangemaakt. GEEN foreign keys gedefinieerd, dat wilde ik doen nadat de query gewoon uberhaupt een beetje loopt.

[ Voor 0% gewijzigd door triet op 24-12-2013 19:10 . Reden: Paar typo's gefixed ]

[Verwijderd]

1.

events_tabel.datumtijd

events_tabel:
id - MEDIUMINT
event_datumtijd - DATETIME
event_omschrijving - tinytext

datumtijd komt niet voor in de tabel: events_tabel, dit moet events_tabel.event_datumtijd zijn.
EDIT: ik zie dat je dit in je post gefixt hebt.


2.

LEFT JOIN betrokkenen_tabel ON events_tabel.id = events_tabel.id

Dit klopt niet helemaal, je vergelijkt dingen die identiek zijn, net zoals 1=1.
EDIT: ik zie dat je dit in je post gefixt hebt.

3. Probeer je query op te splitsen in kleinere problemen die met een query opgelost moeten worden. Dus allereerst: een lijst met events waar 1 of meerdere personen aanwezig bij zijn. In dit geval willen we dus eerst een lijstje met events waar persoon met ID 1 aanwezig is, oftewel:

code:

SELECT events_tabel_id FROM persoon_tabel
LEFT JOIN betrokkenen_tabel ON betrokkenen_tabel.persoon_tabel_id = persoon_tabel.id WHERE betrokkenen_tabel.persoon_tabel_id = 1

Vervolgens wil je een lijstje met events waarvan de eventID's in het lijstje van query 1 (hierboven) voorkomen. Oftewel:

code:

SELECT * FROM events_tabel WHERE id IN(query 1)

Wat dus resulteerd in:

code:

SELECT * FROM events_tabel WHERE id IN(SELECT events_tabel_id FROM persoon_tabel
LEFT JOIN betrokkenen_tabel ON betrokkenen_tabel.persoon_tabel_id = persoon_tabel.id WHERE betrokkenen_tabel.persoon_tabel_id = 1)

Let wel op eventuele typfouten.

[ Voor 114% gewijzigd door Verwijderd op 24-12-2013 19:11 ]

[triet]

Goed, poging twee dan. Ik heb nu een query die snel is en precies doet wat ik wil, MITS de het aantal personen waar ik naar zoek beperkt blijft.

Oftewel: als het aantal records waar ik naar zoek (XXXXXXXX) in zoektabel meevalt (bijv. 10) dan is de query nog wel snel. Maar nu heb ik bijvoorbeeld 10.000 records die ik terugkrijg uit de WHERE zoektabel.id = XXXXXX en dan is de query weer retetraag.

Het probleem zit'm dan dus vooral in de temporary table en hoe ik die vul. Zo is het probleem ietsje kleiner gemaakt ;-) Kan ik die nog optimaliseren?

code:

CREATE TEMPORARY TABLE t1 (
SELECT
  DISTINCT betrokkenen_tabel.events_tabel_id
FROM
  betrokkenen_tabel
INNER JOIN zoektabel ON zoektabel.personen_tabel_id = betrokkenen_tabel.persoon_tabel_id
WHERE zoektabel.id = XXXXXXXX
ORDER BY betrokkenen_tabel.events_tabel_id DESC
LIMIT 100
);

SELECT
  events_tabel.event_datumtijd,
  events_tabel.event_omschrijving,
  persoon_tabel.id,
  persoon_tabel, naam
FROM
  t1
LEFT JOIN events_tabel ON events_tabel.id = t1.events_tabel_id
LEFT JOIN betrokkenen_tabel ON betrokkenen_tabel.events_tabel_id = events_tabel.id
LEFT JOIN persoon_tabel ON persoon_tabel.id = betrokkenen_tabel.persoon_tabel_id

[maxtz0r]

Volgens mij heeft dit te maken met je order by in combinatie met je Limit: http://explainextended.co...ormance-late-row-lookups/

Ik weet niet of dat nog steeds bij mysql van toepassing is maar dat is mijn eerste gok.

[wackmaniac]

Probeer eens een EXPLAIN te draaien op je query, die verteld je redelijk waar je bottleneck zich bevindt.

[NMe]

triet schreef op dinsdag 24 december 2013 @ 18:42:

MySQL:

LEFT JOIN betrokkenen_tabel ON events_tabel.id = betrokkenen_tabel.events_tabel.id

Ik neem aan dat die . daar een tikfout is en het een _ moet zijn, en je die fout alleen hier gemaakt hebt?

Dat werkt maar LOEITRAAG. Ik hoop dat het voorbeeld een beetje duidelijk is. Wat doe ik fout of wat kan beter? Wellicht zelfs de hele structuur overhoop gooien? Ik wil dat deze z.s.m. reageert, maar zeker binnen 1 seconde.

Werkt de query traag of krijg je het resultaat gewoon veel te langzaam overgepompt naar PHP omdat je een te grote resultset in één keer binnen wil halen?

Overigens zijn overal indexes op aangemaakt. GEEN foreign keys gedefinieerd, dat wilde ik doen nadat de query gewoon uberhaupt een beetje loopt.

Wat is "overal"? Op alles velden? Alleen op alle ID's? Op alle ID's én op alles waar je op selecteert/sorteert/filtert? Doorgaans ontbreekt een index of vergeten mensen dat MySQL niet lekker werkt met het gebruiken van meer dan één index per tabel per query.

[drm]

Ik heb je vraag niet helemaal goed doorgelezen, maar het gebruik van INNER ipv LEFT joins maakt ook een enorm verschil. 4 LEFT joins in 1 query is meestal niet zo'n heel goed teken, en in de meeste gevallen ook niet nodig.

[NMe]

Ik zie maar twee left joins?

[drm]

LEFT JOIN over 4 tabellen bedoelde ik

triet

SQL:

SELECT
  events_tabel.event_datumtijd,
  events_tabel.event_omschrijving,
  persoon_tabel.id,
  persoon_tabel, naam
FROM
  t1
LEFT JOIN events_tabel ON events_tabel.id = t1.events_tabel_id
LEFT JOIN betrokkenen_tabel ON betrokkenen_tabel.events_tabel_id = events_tabel.id
LEFT JOIN persoon_tabel ON persoon_tabel.id = betrokkenen_tabel.persoon_tabel_id

[NMe]

Ah, ik had om een of andere reden gemist dat die post van de topicstarter was.

[ACM]

Je zou zoiets kunnen proberen:

SQL:

CREATE TEMPORARY TABLE t1 (
  KEY(events_tabel_id) -- index op t1.events_tabel_id
) AS
SELECT
  DISTINCT betrokkenen_tabel.events_tabel_id
FROM
  betrokkenen_tabel
  JOIN zoektabel ON zoektabel.personen_tabel_id = betrokkenen_tabel.persoon_tabel_id
WHERE zoektabel.id = XXXXXXXX;
-- ORDER BY is nergens voor nodig

En dan daarna het resultaat ophalen:

SQL:

SELECT
  e.event_datumtijd,
  e.event_omschrijving,
  p.id,
  p.naam
FROM
  events_tabel e
 -- geen left joins... maar maakt waarschijnlijk niet zoveel uit voor de performance
  JOIN t1 ON e.id = t1.events_tabel_id
  JOIN betrokkenen_tabel b ON b.events_tabel_id = e.id
  JOIN persoon_tabel p ON p.id = b.persoon_tabel_id
ORDER BY 
  e.event_datumtijd

Het is verder vooral zaak goed naar explain te kijken om te zien waar nog knelpunten zitten. Het kan bijvoorbeeld voor de eerste query zin hebben om een gecombineerde index op betrokkenen_tabel(persoonid, eventid) te hebben. En voor de tweede juist andersom (eventid, persoonid).
Als je daar al een Primary Key op hebt, hoef je uiteraard niet een 2e index toe te voegen

[triet]

ACM schreef op vrijdag 27 december 2013 @ 16:20:
Je zou zoiets kunnen proberen:

SQL:

CREATE TEMPORARY TABLE t1 (
  KEY(events_tabel_id) -- index op t1.events_tabel_id
) AS
SELECT
  DISTINCT betrokkenen_tabel.events_tabel_id
FROM
  betrokkenen_tabel
  JOIN zoektabel ON zoektabel.personen_tabel_id = betrokkenen_tabel.persoon_tabel_id
WHERE zoektabel.id = XXXXXXXX;
-- ORDER BY is nergens voor nodig

De order by is helaas wel nodig. Als ik die achterwege laat gaat alles rap maar ik wil de laatste events hebben (limit 100) en niet "random". Enig idee hoe ik dat anders zou kunnen doen? De joins heb ik aangepast naar je voorbeeld maar dat maakt dus weinig verschil.

Het is verder vooral zaak goed naar explain te kijken om te zien waar nog knelpunten zitten. Het kan bijvoorbeeld voor de eerste query zin hebben om een gecombineerde index op betrokkenen_tabel(persoonid, eventid) te hebben. En voor de tweede juist andersom (eventid, persoonid).
Als je daar al een Primary Key op hebt, hoef je uiteraard niet een 2e index toe te voegen

Ik ga de explain eens even doornemen inderdaad, wordt vervolgd.

[NMe]

triet schreef op woensdag 01 januari 2014 @ 18:36:
[...]

De order by is helaas wel nodig. Als ik die achterwege laat gaat alles rap maar ik wil de laatste events hebben (limit 100) en niet "random". Enig idee hoe ik dat anders zou kunnen doen? De joins heb ik aangepast naar je voorbeeld maar dat maakt dus weinig verschil.

Als die order by ervoor zorgt dat alles traag wordt dan kan ik wel raden waar een index mist of niet gebruikt wordt.

Wat ACM volgens mij bedoelt is trouwens dat je de ordering en limit beiden in je query zet die gebruik maakt van je temporary table. Ik ben nog wat te duf van gisteren om daar zelf inhoudelijk iets over te zeggen.

[triet]

code:

EXPLAIN SELECT
  DISTINCT betrokkenen_tabel.events_tabel_id
FROM
  betrokkenen_tabel
JOIN zoektabel ON zoektabel.personen_tabel_id = betrokkenen_tabel.persoon_tabel_id
WHERE zoektabel.id = XXXXXXX
ORDER BY betrokkenen_tabel.events_tabel_id DESC
LIMIT 100
);

geeft de volgende output:

code:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  zoektabel     ALL   id,personen_tabel_id    NULL    NULL    NULL    7606    Using where; Using temporary; Using filesort
1   SIMPLE  betrokkenen_tabel   ref persoon_tabel_id    persoon_tabel_id    3   zoektabel.persoon_tabel_id  208 Using where

Begrijp ik nou goed dat hij wel een index ziet maar die niet gebruikt? Als ik de "order by" weglaat verdwijnt ook de "Using filesort" uiteraard. Voor de rest blijft het hetzelfde maar is de query binnen zoveel ms klaar. Door die order duurt'ie > 1 minuut...

[ACM]

triet schreef op woensdag 01 januari 2014 @ 18:36:
De order by is helaas wel nodig. Als ik die achterwege laat gaat alles rap maar ik wil de laatste events hebben (limit 100) en niet "random". Enig idee hoe ik dat anders zou kunnen doen? De joins heb ik aangepast naar je voorbeeld maar dat maakt dus weinig verschil.

De order by is toch niet per se daar nodig?
Er zit niet voor niets een order by e.event_datumtijd in mijn 2e query en geen limit op de eerste. Wellicht moet daar nog een 'DESC' aan toe worden gevoegd...

Maar ik heb de sortering en aanvullende informatie expres losgetrokken van de basisselectie van events, want dat was volgens jouw informatie juist traag

Dus lees graag beide queries samen en probeer niet een halve variant uit, want dan krijg je of niet de goede data of domweg je oude situatie terug.
Ik garandeer overigens niet dat het effectief is, maar dit soort herschrijvingen kunnen soms enorm uitmaken.

triet schreef op woensdag 01 januari 2014 @ 19:11:
Begrijp ik nou goed dat hij wel een index ziet maar die niet gebruikt?

Dat zal wel komen omdat er gesorteerd wordt op een veld waar geen index mogelijk is in combinatie met de id-zoekopdracht...
Vandaar ook dat ik dat uit elkaar trok. Eerst al je event's selecteren en daarna pas de nieuwste X eruit halen met aanvullende informatie.

Overigens zou ik wel verwachten dat ie dan de id-index van je 'zoektabel' zou gebruiken. Daar een use of force index op doen (maar eerst 'ANALYZE TABLE zoektabel' om te zien of het daarna beter gaat) kan helpen... maar verandert de basisvorm van je query natuurlijk niet.