zeer trage subquery met NOT IN

[Verwijderd]

Ik werk met mysql 5.0.89 (de meest recente die op mijn server kan draaien) en heb de volgende query

SQL:

select verzuimmeldingID FROM verzuimmeldingen v WHERE v.verzuimmeldingID NOT IN
  (select s.verzuimmeldingID FROM pd_schedule s 
WHERE s.from_actie_id=13 AND s.verzuimmeldingID>0)

Op de velden van verzuimmeldingID en from_actie_id zit een index. Uitvoering van deze query duurt maar liefst 255 seconden. De tabel verzuimmeldingen bevat 11500 records, pd_schedule bevat 27500 records. Naar mijn mening zou dit veel sneller moeten kunnen.
Een normale query als hieronder presteert niet echt spectaculair beter, het duurt namelijk nog steeds 112 seconden.

SQL:

select v.verzuimmeldingID FROM verzuimmeldingen v 
LEFT JOIN pd_schedule s ON 
s.verzuimmeldingID=v.verzuimmeldingID AND 
s.verzuimmeldingID=null

Duren dit soort queries gewoon zo lang, of is er wat mis met mijn indexen of configuratie?

[CodeCaster]

Wat zegt een EXPLAIN, en over welke kolommen staan je indexen?

[dusty]

zijn de indexen wel gezet in beide tabellen?

Is de Foreign Key-restrictie gezet tussen de twee tabellen?

[Remus]

IN en NOT IN zijn vaak slechte performers. Probeer je query om te zetten naar een NOT EXISTS ipv een NOT IN.

Overigens zou die LEFT JOIN op zich redelijk moeten performen zolang je goede indices hebt.

[ Voor 48% gewijzigd door Remus op 11-01-2010 14:25 ]

[Verwijderd]

CodeCaster schreef op maandag 11 januari 2010 @ 14:17:
Wat zegt een EXPLAIN, en over welke kolommen staan je indexen?

Ik krijg dit als antwoord

code:

+----+--------------------+-------+----------------+------------------+------------------+---------+------+-------+--------------------------+
| id | select_type        | table | type           | possible_keys    | key              | key_len | ref  | rows  | Extra                    |
+----+--------------------+-------+----------------+------------------+------------------+---------+------+-------+--------------------------+
| 1  | PRIMARY            | v     | index          | NULL             | PRIMARY          | 4       | NULL | 11545 | Using where; Using index |
| 2  | DEPENDENT SUBQUERY | s     | index_subquery | verzuimmeldingID | verzuimmeldingID | 5       | func | 4     | Using index              |
+----+--------------------+-------+----------------+------------------+------------------+---------+------+-------+--------------------------+

2 rows in set (0.03 sec)

[Verwijderd]

dusty schreef op maandag 11 januari 2010 @ 14:20:
zijn de indexen wel gezet in beide tabellen?

Is de Foreign Key-restrictie gezet tussen de twee tabellen?

Indexen? Volgens mij wel ja
Foreign Key's : niets ingesteld, zou dat moeten/beter zijn?
Dit zijn trouwens de indexen

CodeCaster schreef op maandag 11 januari 2010 @ 14:17:
Wat zegt een EXPLAIN, en over welke kolommen staan je indexen?

De indexen van verzuimmeldingen

code:

+------------------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name             | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| verzuimmeldingen | 0          | PRIMARY              | 1            | verzuimmeldingID | A         | 11545       | NULL     | NULL   |      | BTREE      |         |
| verzuimmeldingen | 1          | oorzaakID            | 1            | oorzaakID        | A         | 13          | NULL     | NULL   | YES  | BTREE      |         |
| verzuimmeldingen | 1          | statusID             | 1            | statusID         | A         | 962         | NULL     | NULL   | YES  | BTREE      |         |
| verzuimmeldingen | 1          | idx_verzuimmeldingID | 1            | verzuimmeldingID | A         | 11545       | NULL     | NULL   |      | BTREE      |         |
| verzuimmeldingen | 1          | idx_werknemerID      | 1            | verzuimmeldingID | A         | 11545       | NULL     | NULL   |      | BTREE      |         |
| verzuimmeldingen | 1          | idx_werknemerID      | 2            | werknemerID      | A         | 11545       | NULL     | NULL   | YES  | BTREE      |         |
+------------------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+

en van pd_schedule

code:

+-------------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| pd_schedule | 0          | PRIMARY          | 1            | schedule_id      | A         | 27559       | NULL     | NULL   |      | BTREE      |         |
| pd_schedule | 1          | verzuimmeldingID | 1            | verzuimmeldingID | A         | 13779       | NULL     | NULL   | YES  | BTREE      |         |
| pd_schedule | 1          | werkgeverID      | 1            | werkgeverID      | A         | 100         | NULL     | NULL   | YES  | BTREE      |         |
| pd_schedule | 1          | werknemerID      | 1            | werknemerID      | A         | 3062        | NULL     | NULL   | YES  | BTREE      |         |
| pd_schedule | 1          | status           | 1            | status           | A         | 11          | NULL     | NULL   |      | BTREE      |         |
| pd_schedule | 1          | from_actie_id    | 1            | from_actie_id    | A         | 84          | NULL     | NULL   |      | BTREE      |         |
| pd_schedule | 1          | from_proces_id   | 1            | from_proces_id   | A         | 35          | NULL     | NULL   |      | BTREE      |         |
| pd_schedule | 1          | gebruikersID     | 1            | gebruikersID     | A         | 153         | NULL     | NULL   |      | BTREE      |         |
+-------------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+

[ Voor 85% gewijzigd door Verwijderd op 11-01-2010 14:34 ]

[cariolive23]

s.verzuimmeldingID=null

Dit kan niet, NULL is namelijk nooit gelijk aan NULL, NULL is hooguit ook NULL. Gebruik dus IS NULL, een speciale vergelijking voor de NULL's:

s.verzuimmeldingID IS NULL

[ACM]

SQL:

select verzuimmeldingID FROM verzuimmeldingen v WHERE v.verzuimmeldingID NOT IN
  (select s.verzuimmeldingID FROM pd_schedule s 
WHERE s.from_actie_id=13 AND s.verzuimmeldingID>0)

Dit is niet de meest efficiente toepassing van NOT IN, een NOT EXISTS kan hier meerwaarde bieden.

SQL:

select v.verzuimmeldingID FROM verzuimmeldingen v 
LEFT JOIN pd_schedule s ON 
s.verzuimmeldingID=v.verzuimmeldingID AND 
s.verzuimmeldingID=null

Deze query is toch helemaal niet hetzelfde?

Je zal sowieso WHERE s.verzuimmeldingID IS NULL moeten doen (en niet in je left join and-en)

[Verwijderd]

cariolive23 schreef op maandag 11 januari 2010 @ 14:39:
[...]

Dit kan niet, NULL is namelijk nooit gelijk aan NULL, NULL is hooguit ook NULL. Gebruik dus IS NULL, een speciale vergelijking voor de NULL's:

s.verzuimmeldingID IS NULL

Ik heb nu dit geprobeerd en de uitvoeringtijd is nu 112 seconden, nog steeds erg lang volgens mij.

SQL:

SELECT v.verzuimmeldingID FROM verzuimmeldingen v 
LEFT JOIN pd_schedule s 
ON (s.verzuimmeldingID=v.verzuimmeldingID 
AND s.verzuimmeldingID IS NULL)

Maar... dit gaat veeeeel sneller (0,74 sec)

SQL:

SELECT v.verzuimmeldingID FROM verzuimmeldingen v 
LEFT JOIN pd_schedule s ON
 (s.verzuimmeldingID=v.verzuimmeldingID) 
WHERE  s.verzuimmeldingID IS NULL

Het lijkt er op dat ik de oplossing met jullie hulp gevonden heb

[ Voor 20% gewijzigd door Verwijderd op 11-01-2010 14:57 ]

[Soultaker]

De query plan maakt wel duidelijk waarom het zo traag is: MySQL ziet die subquery als een dependent subquery, dus afhankelijk van de main query. Dat betekent dat 'ie, in plaats van één keer de subquery uit te voeren en het resultaat te gebruiken om de resultaten van de main query te filteren, voor élke rij in de main query de subquery uit gaat voeren. Dat wordt traag.

Dat is natuurlijk stom van MySQL's query optimizer. Maar je kunt er wel omheenwerken, bijvoorbeeld met een OUTER JOIN:

SQL:

SELECT verzuimmeldingID FROM verzuimmeldingen v
LEFT OUTER JOIN pd_schedule s ON
    s.from_actie_id=13 AND
    s.verzuimmeldingID > 0 AND
    s.verzuimmeldingID = v.verzuimmeldingID
WHERE s.verzuimmeldingID IS NULL

[Soultaker]

ACM schreef op maandag 11 januari 2010 @ 14:44:

SQL:

select v.verzuimmeldingID FROM verzuimmeldingen v 
LEFT JOIN pd_schedule s ON 
s.verzuimmeldingID=v.verzuimmeldingID AND 
s.verzuimmeldingID=null

Deze query is toch helemaal niet hetzelfde?

Werkt dit Wellicht heb ik recent te weinig geSQLd, maar een JOIN is standaard toch een inner join, en dan worden rijen uit v waarbij geen matchende rij in s gevonden kan worden weggelaten, terwijl je die júist wil hebben? En is s.verzuimmeldingID=null niet áltijd onwaar?

[dusty]

Verwijderd schreef op maandag 11 januari 2010 @ 14:28:
[...]
Foreign Key's : niets ingesteld, zou dat moeten/beter zijn?
[...]

Een Ja voor je data-integriteit zou dat moeten en een Ja voor je performance zou dat beter zijn.

[cariolive23]

Verwijderd schreef op maandag 11 januari 2010 @ 14:50:
[...]

Ik heb nu dit geprobeerd en de uitvoeringtijd is nu 112 seconden, nog steeds erg lang volgens mij.

SQL:

SELECT v.verzuimmeldingID FROM verzuimmeldingen v 
LEFT JOIN pd_schedule s 
ON (s.verzuimmeldingID=v.verzuimmeldingID 
AND s.verzuimmeldingID IS NULL)

Maar... dit gaat veeeeel sneller (0,74 sec)

SQL:

SELECT v.verzuimmeldingID FROM verzuimmeldingen v 
LEFT JOIN pd_schedule s ON
 (s.verzuimmeldingID=v.verzuimmeldingID) 
WHERE  s.verzuimmeldingID IS NULL

Het lijkt er op dat ik de oplossing met jullie hulp gevonden heb

Dit zijn twee totaal verschillende queries, die kun je dus niet met elkaar vergelijken. Ga je SQL voortaan netjes uitschrijven, dan zie je zelf ook dat er in de eerste query iets goed fout gaat:

SQL:

SELECT 
    v.verzuimmeldingID 
FROM 
    verzuimmeldingen v 
        LEFT JOIN pd_schedule s  ON (s.verzuimmeldingID=v.verzuimmeldingID AND s.verzuimmeldingID IS NULL); 
-- zie de fout, s.verzuimmeldingID kan onmogelijk gelijk zijn aan v.verzuimmeldingID én NULL zijn

-- tweede query:
SELECT 
    v.verzuimmeldingID 
FROM 
    verzuimmeldingen v 
        LEFT JOIN pd_schedule s ON (s.verzuimmeldingID=v.verzuimmeldingID) 
WHERE  
    s.verzuimmeldingID IS NULL;

[Verwijderd]

cariolive23 schreef op maandag 11 januari 2010 @ 15:01:
[...]

Dit zijn twee totaal verschillende queries, die kun je dus niet met elkaar vergelijken. Ga je SQL voortaan netjes uitschrijven, dan zie je zelf ook dat er in de eerste query iets goed fout gaat:

SQL:

SELECT 
    v.verzuimmeldingID 
FROM 
    verzuimmeldingen v 
        LEFT JOIN pd_schedule s  ON (s.verzuimmeldingID=v.verzuimmeldingID AND s.verzuimmeldingID IS NULL); 
-- zie de fout, s.verzuimmeldingID kan onmogelijk gelijk zijn aan v.verzuimmeldingID én NULL zijn

-- tweede query:
SELECT 
    v.verzuimmeldingID 
FROM 
    verzuimmeldingen v 
        LEFT JOIN pd_schedule s ON (s.verzuimmeldingID=v.verzuimmeldingID) 
WHERE  
    s.verzuimmeldingID IS NULL;

Maar hoe kan het dan dat de eerste query wel een juist resultaat geeft (maar na een heel lange tijd)?
En hoe zou je die eerste query dan op een correcte manier maken?
Je moet toch een join maken door s.verzuimmeldingID=v.verzuimmeldingID maar vervolgens alleen die records tonen waarvoor geen gerelateerde records in pd_schedule zitten?

[ACM]

Soultaker schreef op maandag 11 januari 2010 @ 14:53:
Werkt dit

Dat was mijn punt (ik quote zijn eigen query) Maar ik probeer het niet altijd gelijk helemaal voor te zeggen.

En is s.verzuimmeldingID=null niet áltijd onwaar?

Uhuh, maar ook dat was al eerder genoemd.

[Soultaker]

Oh zo; ik dacht even dat je het als "goede" aanpak postte, maar nu ik het nog eens terug lees zie ik dat je dat niet bedoelde.

Verwijderd schreef op maandag 11 januari 2010 @ 15:08:
Maar hoe kan het dan dat de eerste query wel een juist resultaat geeft (maar na een heel lange tijd)?

MySQL doet wel meer rare dingen, maar waarom je hier überhaupt resultaten uit krijgt is me een raadsel. Weet je zeker dat je de goede query aan 't uitvoeren bent?

[ Voor 59% gewijzigd door Soultaker op 11-01-2010 15:25 ]

[Verwijderd]

dusty schreef op maandag 11 januari 2010 @ 14:55:
[...]

Een Ja voor je data-integriteit zou dat moeten en een Ja voor je performance zou dat beter zijn.

Ik draai MyIsam en daar lijken fk's niet mogelijk, weet je of ik de structuur zonder problemen om kan zetten naar InnoDB om fk's mogelijk te maken?

[cariolive23]

Ik draai MyIsam en daar lijken fk's niet mogelijk

Klopt, daar kun je geen relationele database mee maken. Je kunt de boel wel vrij eenvoudig omzetten naar innoDB, al kun je dan weer geen fulltext search doen. Al hoeft dat geen probleem te zijn, je zal het niet altijd gebruiken en er zijn betere oplossingen (Sphinx).

Hoe MySQL een query uitvoert, is volgens mij niet geheel inzichtelijk maar je moet altijd met EXPLAIN aan de slag. Daarnaast heb je goede testcases nodig, zonder goede testcases (die je nog niet hebt getoond) kun je niet zeggen of een resultaat goed is. MySQL kent nogal wat bijzonder features/bugs, daar mag je niet zomaar op vertrouwen. Heb je MySQL/de connectie wel van een functioneel goede configuratie voorzien? Dat is onmisbaar, anders wordt het echt onbetrouwbaar. Dan is 31 februari ineens een geldige datum, kun je appels en peren bij elkaar optellen, de meest bijzonder waardes krijgen, etc. etc.

http://wiki.phpfreakz.nl/Werken_met_MySQL#Configuratie