[MySQL] Query blijft hangen

[Verwijderd]

Ik werk aan een Fotodatabase waar je op allerlei manieren je foto's kan indexeren. Nu heb ik vorige week een patch van iemand geimplementeerd en na wat testen kom ik erachter dat ik vanaf de zoekpagina de MySQL daemon zo goed als plat kan leggen. Het gaat om een query die automatisch gegenereerd wordt vanaf de zoekpagina. Het gaat fout als je zoekt op 2 velden uit dezelfde tabel. Omdat de applicatie op dit moment nog compatible moet zijn met MySQL 4.0 en ouder, kan ik geen subqueries gebruiken. Na wat debuggen ben ik er achter gekomen dat dit de query is waar hij op "stikt":

SQL:

select count(distinct ph.photo_id) from 
zoph_photos as ph, 
zoph_photo_albums as pa0, 
zoph_photo_albums as pa1 
where 
(pa0.album_id in (30) and pa0.photo_id = ph.photo_id) 
or 
(pa1.album_id in (22) and pa1.photo_id = ph.photo_id);

Ik heb dezelfde query direct in MySQL ingetikt en na een hele nacht was er nog geen output van de query. De reden dat er gebruik gemaakt wordt van "in (xxx)" en niet "=xxx" is dat er meerdere albums tegelijk opgevraagd kunnen worden, omdat albums ook andere albums kunnen bevatten.
Voorbeeldje

code:

Albums
  Dieren      (1)
     Katten   (2)
     Honden  (3)
     Vissen   (4)
  Auto's       (5)
     Volvo     (6)
     Peugeot (7)

Als je nu in de webgui het album "Dieren" kiest, zal de query worden "in (1,2,3,4)".
Je kan echter ook kiezen voor, "Katten" OR "Honden". Dat zal een vergelijkbare query genereren als bovenstaande (behalve dat daar de keys 22 en 30 ipv 2 en 3 gebruikt worden).

Als je kiest voor "Katten" AND "Honden" (dus foto's die zowel in het album katten als in het album honden staan) dan wordt deze query gegenereerd (dezelfde dus, alleen OR -> AND):

SQL:

select count(distinct ph.photo_id) from 
zoph_photos as ph, 
zoph_photo_albums as pa0, 
zoph_photo_albums as pa1 
where 
(pa0.album_id in (30) and pa0.photo_id = ph.photo_id) 
and 
(pa1.album_id in (22) and pa1.photo_id = ph.photo_id);

Deze query werkt wel en is ook binnen een seconde klaar.

Nu kan ik natuurlijk de code herschrijven dat bovenstaande query veranderd wordt in "in (22, 30)"; die heb ik via de MySQL CLI geprobeerd en dat werkt; echter, ik zou toch graag willen weten waarom deze query niet werkt... dat hij minder efficient is dat "in (22, 30)" begrijp ik, waarom hij helemaal niet werkt (of heel erg lang duurt om uit te voeren) niet.

Het liefste wil ik de query zo houden, omdat de code die deze query genereerd voor de AND er toch in moet blijven, omdat ik zonder subqueries dat eigenlijk niet anders op kan lossen.

Tenslotte nog de explain van de eerste query:

SQL:

mysql> explain select count(distinct ph.photo_id) from zoph_photos as 
ph, zoph_photo_albums as pa0, zoph_photo_albums as pa1 where 
(pa0.album_id in (30) and pa0.photo_id = ph.photo_id) or (pa1.album_id 
in (22) and pa1.photo_id = ph.photo_id);
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | ph    | index | PRIMARY       | PRIMARY |       4 | NULL | 4627 | Using index             |
|  1 | SIMPLE      | pa0   | index | PRIMARY       | PRIMARY |       8 | NULL | 4777 | Using index              |
|  1 | SIMPLE      | pa1   | index | PRIMARY       | PRIMARY |       8 | NULL | 4777 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
3 rows in set (0.02 sec)

Update:
Nog even wat testen gedaan... als ik "in (xxx)" vervang door "=xxx" heb ik hetzelfde probleem.

Update2:
Weer wat aan het testen geweest... als ik de query als volgt opschrijf:

SQL:

select count(distinct ph.photo_id) from zoph_photos as ph 
join zoph_photo_albums as pa0 on pa0.photo_id=ph.photo_id 
join zoph_photo_albums as pa1 on pa1.photo_id=ph.photo_id 
where pa0.album_id=30 or pa1.album_id=22;

werkt het wel. Terwijl dat volgens mij dezelfde query zou moeten zijn... iemand die dat kan bevestigen of weerleggen?

Hij werkt ook als ik de laatste regel verander in

SQL:

where pa0.album_id in (30) or pa1.album_id in (22);

De explain van deze query is trouwens behoorlijk verschillend van de oorspronkelijke

SQL:

mysql> explain select count(distinct ph.photo_id) from zoph_photos as ph join zoph_photo_albums as pa0 on pa0.photo_id=ph.photo_id join zoph_photo_albums as pa1 on pa1.photo_id=ph.photo_id where pa0.album_id in (30) or pa1.album_id in (22);
+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                  | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+--------------------------+
|  1 | SIMPLE      | pa0   | index  | PRIMARY       | PRIMARY |       8 | NULL                 | 4777 | Using index              |
|  1 | SIMPLE      | ph    | eq_ref | PRIMARY       | PRIMARY |       4 | zophdev.pa0.photo_id |    1 | Using index              |
|  1 | SIMPLE      | pa1   | ref    | PRIMARY       | PRIMARY |       4 | zophdev.ph.photo_id  |   47 | Using where; Using index |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+--------------------------+

Maak ik nou een fout of loop ik tegen een bug in de optimizer aan?

[ Voor 26% gewijzigd door Verwijderd op 19-01-2006 21:06 ]

[Verwijderd]

(schop)

[curry684]

Verwijderd schreef op donderdag 19 januari 2006 @ 11:48:
Update2:
Weer wat aan het testen geweest... als ik de query als volgt opschrijf:

SQL:

select count(distinct ph.photo_id) from zoph_photos as ph 
join zoph_photo_albums as pa0 on pa0.photo_id=ph.photo_id 
join zoph_photo_albums as pa1 on pa1.photo_id=ph.photo_id 
where pa0.album_id=30 or pa1.album_id=22;

werkt het wel. Terwijl dat volgens mij dezelfde query zou moeten zijn... iemand die dat kan bevestigen of weerleggen?

Tis dat je zelf al met deze syntax kwam anders had ik het voorgesteld. Ze zijn namelijk niet equivalent, allesbehalve zelfs. Je hebt nu een query die 3 tables joint op 2 condities, en vervolgens over de resultset een simpel filter loslaat. Je oude query in deprecated, obsolete en archaische syntax impliceerde dat de filters onderdeel waren van de join. Een brakke database als MySQL kan daar blijkbaar zoals met zoveel dingen niet goed mee omgaan: je loopt niet zozeer tegen een bug in de optimizer aan als wel tegen de complete absentie van een optimizer in MySQL.

Des te meer redenen om gewoon de normale expliciete syntax voor joins te gebruiken en de deprecated, obsolete en in dit soort gevallen volgens MySQL ambigue syntax te vermijden.

[Verwijderd]

curry684 schreef op vrijdag 20 januari 2006 @ 12:07:
Tis dat je zelf al met deze syntax kwam anders had ik het voorgesteld. Ze zijn namelijk niet equivalent, allesbehalve zelfs.

Maar áls hij zou werken zou hij met dezelfde results komen, toch?
Overigens hoorde ik van degene die de patch heeft gemaakt (en dus ook de code die deze query generereert) dat het op zijn testmachine wel werkt (MySQL 5.0.x), hij heeft het ook nog getest op een MySQL 4.0 machine en daar werkt het ook niet. Ik heb al even door de MySQL bug db gelopen maar kon daar niks vinden. Misschien even een bugje inschieten.

Maar goed, ik denk dat het niet zo moeilijk moet zijn om de code te herschrijven naar de "JOIN ... ON" syntax. (die ik overigens zelf ook meestal gebruik omdat ik het gewoon duidelijker te lezen vind)

[Varienaja]

Verwijderd schreef op donderdag 19 januari 2006 @ 11:48:

SQL:

select count(distinct ph.photo_id) from 
zoph_photos as ph, 
zoph_photo_albums as pa0, 
zoph_photo_albums as pa1 
where 
(pa0.album_id in (30) and pa0.photo_id = ph.photo_id) 
or 
(pa1.album_id in (22) and pa1.photo_id = ph.photo_id);

Wat jij moet doen is:

SQL:

select count(distinct ph.photo_id) from 
zoph_photos as ph, 
zoph_photo_albums as pa0, 
where 
(pa0.album_id in (22,30) and pa0.photo_id = ph.photo_id) ;

Nu kan ik natuurlijk de code herschrijven dat bovenstaande query veranderd wordt in "in (22, 30)";

Had je al gedaan dus.

Jouw eerste versie is zo traag, omdat wanneer je een 'or' gebruikt kan ofwel aan de ene voorwaarde voldaan worden (pa0.album_id in (22) and pa0.photo_id = ph.photo_id) ofwel aan de andere voorwaarde pa1.album_id in (22) and pa1.photo_id = ph.photo_id.

In beide gevallen heb je een tabel in je select staan die niet in de where terugkomt. Dit levert een (vaak enorm) cartesisch product op wat er inderdaad voor kan zorgen dat je pc dagenlang aan het rammelen slaat. Overigens is het antwoord als je dat zou krijgen totaal niet wat je zoekt/verwacht. De query is gewoon helemaal fout.

Je moet echt meerdere variabelen in de 'in' gaan stoppen, dan krijg je netjes wat je verwacht.

[ Voor 47% gewijzigd door Varienaja op 20-01-2006 14:20 ]

[Verwijderd]

Varienaja schreef op vrijdag 20 januari 2006 @ 14:09:
Wat jij moet doen is:

Zie startpost:

Nu kan ik natuurlijk de code herschrijven dat bovenstaande query veranderd wordt in "in (22, 30)"; die heb ik via de MySQL CLI geprobeerd en dat werkt; echter, ik zou toch graag willen weten waarom deze query niet werkt... dat hij minder efficient is dat "in (22, 30)" begrijp ik, waarom hij helemaal niet werkt (of heel erg lang duurt om uit te voeren) niet.

Bovendien... deze code werkt niet voor "AND", want dat heb je óf de dubbele JOIN óf subqueries nodig, daarnaast, de code wordt automagisch gegenereerd en een dergelijke constructie is zowiezo nodig als de zoekopdracht niet is "album 30 or album 22" maar "album 30 or category 17", en ik wil natuurlijk zo min mogelijk verschillende code hebben...

[ Voor 27% gewijzigd door Verwijderd op 20-01-2006 14:20 . Reden: toevoeging onder de quote ]

[Verwijderd]

Varienaja schreef op vrijdag 20 januari 2006 @ 14:09:
Overigens is het antwoord als je dat zou krijgen totaal niet wat je zoekt/verwacht. De query is gewoon helemaal fout.

Het antwoord wat ik terug zou krijgen klopt wel, op MySQL 5.0 doet hij het wel goed.

[Varienaja]

Verwijderd schreef op vrijdag 20 januari 2006 @ 14:23:
Het antwoord wat ik terug zou krijgen klopt wel, op MySQL 5.0 doet hij het wel goed.

Daarnet schreef je nog dat je de hele nacht op mysql had zitten wachten..

Maar, waarom doe je niet zo:

SQL:

select count(distinct pa0.photo_id) from 
zoph_photo_albums as pa0, 
where 
(pa0.album_id=22 or pa0.album_id=30);

Dan ben je van het hele and/or gedoe af.

[GambitRS]

Tip: probeer bij dit soort vage queries eerst eens met een aantal testtabellen waar je maar 4 of 5 entries plaatst, dan kan je aan de hand daarvan kijken of het behaalde resultaat de juiste is. Ook al krijg je dan een carthesisch product, je ziet dan in ieder geval altijd redelijk snel het antwoord. Door een select * te doen zie je dan ook gelijk de manier waarop de tabellen aan elkaar zijn gekoppeld.

Wat dacht je trouwens van:

SQL:

select count(distinct photo_id) 
from zoph_photo_albums
where album_id in (20,30)

[Verwijderd]

Varienaja schreef op vrijdag 20 januari 2006 @ 14:30:
Daarnet schreef je nog dat je de hele nacht op mysql had zitten wachten..

Overigens hoorde ik van degene die de patch heeft gemaakt (en dus ook de code die deze query generereert) dat het op zijn testmachine wel werkt (MySQL 5.0.x)

select count(distinct photo_id)
from zoph_photo_albums
where album_id in (20,30)

Dat kan niet, want je kan ook op velden zoeken die in de tabel zoph_photos staan. De query wordt automatisch gegenereerd op basis van een zoekformulier in html/php. Ik wil zoveel mogelijk voor elke zoekactie dezelfde basisquery gebruiken die dan uitgebreid wordt op basis van de velden die je ingevuld hebt. Wat het lastig maakt, is dat ik geen subqueries wil gebruiken omdat ik het voorlopig nog compatible wil houden met MySQL 4.0.

[ Voor 43% gewijzigd door Verwijderd op 20-01-2006 15:58 ]

[GambitRS]

Hoezo kan niet? Dat is juist heel erg makkelijk:

SQL:

Select * 
From zoph_photos P
Inner join zoph_photo_albums A
On P.photo_id = A.photo_id
Where A.album_id = 20 OR A.album_id = 30

[ Voor 3% gewijzigd door GambitRS op 21-01-2006 01:44 ]

[ACM]

Een deels herhalend wat al eerder geroepen is:

Verwijderd schreef op donderdag 19 januari 2006 @ 11:48:

SQL:

select count(distinct ph.photo_id) from 
zoph_photos as ph, 
zoph_photo_albums as pa0, 
zoph_photo_albums as pa1 
where 
(pa0.album_id in (30) and pa0.photo_id = ph.photo_id) 
or 
(pa1.album_id in (22) and pa1.photo_id = ph.photo_id);

Hij neemt het carthetisch product van deze drie tabellen (zie de hoeveelheid rows in de explain) en gaat daarna pas kijken of ze wel voldoen aan je where-clause. Echt heel anders kan het ook niet, want zoals al gezegd test je in beide OR's maar 2 van de 3 tabellen. MySQL (tot 5.0 iig) is niet zo sterk in het optimaliseren van queries met OR's erin waar die voor compleet gescheiden branches zorgen, maar jouw carthetisch product is gewoon lastig te optimaliseren.

Ik heb dezelfde query direct in MySQL ingetikt en na een hele nacht was er nog geen output van de query. De reden dat er gebruik gemaakt wordt van "in (xxx)" en niet "=xxx" is dat er meerdere albums tegelijk opgevraagd kunnen worden, omdat albums ook andere albums kunnen bevatten.

Met 4627 * 4777 * 4777 records door worstelen kan ik me voorstellen dat ie er even over doet ja.

SQL:

select count(distinct ph.photo_id) from 
zoph_photos as ph, 
zoph_photo_albums as pa0, 
zoph_photo_albums as pa1 
where 
(pa0.album_id in (30) and pa0.photo_id = ph.photo_id) 
and 
(pa1.album_id in (22) and pa1.photo_id = ph.photo_id);

Deze query werkt wel en is ook binnen een seconde klaar.

Hier worden de drie tabellen dan ook via de AND in de where aan elkaar gekoppeld.

Nu kan ik natuurlijk de code herschrijven dat bovenstaande query veranderd wordt in "in (22, 30)"; die heb ik via de MySQL CLI geprobeerd en dat werkt; echter, ik zou toch graag willen weten waarom deze query niet werkt... dat hij minder efficient is dat "in (22, 30)" begrijp ik, waarom hij helemaal niet werkt (of heel erg lang duurt om uit te voeren) niet.

Hij werkt prima, maar duurt alleen erg lang. Wat je in wezen doet is 2 sets van records maken, namelijk een die aan de eerste tak van de OR voldoet en een die aan de tweede voldoet. Die twee ga je kruisgewijs tegenover elkaar zetten omdat die verder niet meer met elkaar verbonden worden en SQL standaard een carthetisch product er van maakt.
Bovendien lijkt het er op dat MySQL < 5.0 dat dan ook nog eens inefficient doet, want in principe hoef je natuurlijk maar een stuk of honderd * een stuk of honderd items te joinen, door eerst de losse OR-takken uit te zoeken en dan pas de joins uit te voeren, ipv die 4627 * 4777 * 4777 records. Ik vermoed dat dat dan ook precies de optimalisatie is die ze in 5.0 (eindelijk) ingevoerd hebben.

SQL:

select count(distinct ph.photo_id) from zoph_photos as ph 
join zoph_photo_albums as pa0 on pa0.photo_id=ph.photo_id 
join zoph_photo_albums as pa1 on pa1.photo_id=ph.photo_id 
where pa0.album_id=30 or pa1.album_id=22;

werkt het wel. Terwijl dat volgens mij dezelfde query zou moeten zijn... iemand die dat kan bevestigen of weerleggen?

Dit is niet dezelfde query, laat de distinct eens weg? In dit geval neem je namelijk de photos-tabel en plak je via die photo_id's de photo_albums-tabel er aan vast. En dan ga je pas kijken of ie in album_id 30 of 22 zit, maar je hebt al een veel minder grote set om doorheen te worstelen. In plaats van alle records uit photos, photo_albums 0 en photo_albums 1 kruislings aan elkaar gekoppeld heb je nu nog slechts die koppels waar de photo_id's gelijk zijn.

Je hebt kans dat deze query dan ook redelijk vlot werkt:

SQL:

select count(distinct ph.photo_id) from zoph_photos as ph, 
zoph_photo_albums as pa0,
zoph_photo_albums as pa1
where 
(pa0.photo_id=ph.photo_id AND pa1.photo_id=ph.photo_id)
AND
(pa0.album_id=30 or pa1.album_id=22)

Zelf zou ik in dit geval de join-versie nemen.

Hij werkt ook als ik de laatste regel verander in

SQL:

where pa0.album_id in (30) or pa1.album_id in (22);

IN is slechts een manier om een hoop =-en met een OR op te geven, met een enkel getal is het dus precies hetzelfde als de =

De explain van deze query is trouwens behoorlijk verschillend van de oorspronkelijke

Je kunt er leuk aan zien dat je inderdaad slechts de records uit je photos-tabel krijgt met daaraan geplakt de bijbehorende photo_album-records. En dan uiteraard nog ietsje efficienter opgehaald dan de theoretische aanpak die ik hierboven beschrijf.

Maak ik nou een fout of loop ik tegen een bug in de optimizer aan?

Jij maakt een fout door het carthetisch product te genereren. Maar de optimiser zou eventueel trucjes kunnen uithalen om het wat efficienter te doen, echt heel efficient kan ie niet worden door die OR, maar in de multi-seconde range is natuurlijk een stuk beter dan de multi-dagen range

curry684 schreef op vrijdag 20 januari 2006 @ 12:07:
Je oude query in deprecated, obsolete en archaische syntax impliceerde dat de filters onderdeel waren van de join.

Waarom is dit deprecated en sinds welke SQL-versie is dat het geval dan?

je loopt niet zozeer tegen een bug in de optimizer aan als wel tegen de complete absentie van een optimizer in MySQL.

Je kan ook overdrijven Er is wel een optimiser, alleen niet zo'n enorm goeie.

Des te meer redenen om gewoon de normale expliciete syntax voor joins te gebruiken en de deprecated, obsolete en in dit soort gevallen volgens MySQL ambigue syntax te vermijden.

Ambigue? Waarom? Of eigenlijk meer, waarom alleen volgens MySQL?
Hij dwingt gewoon een carthetisch product af, dan kan MySQL alleen nog maar leuke trucjes uithalen om het wat efficienter te doen, maar het blijft een carthetisch product.
En het is dan inderdaad wel wat jammer dat MySQL < 5.0 de bijbehorende trucjes niet kent...

[ACM]

GambitRS schreef op zaterdag 21 januari 2006 @ 01:43:
Hoezo kan niet? Dat is juist heel erg makkelijk:

En als ie nou in zowel 20 als 30 voor moet komen?

[Verwijderd]

Hoezo kan niet?

SQL:

Select * 
From zoph_photos P
Inner join zoph_photo_albums A
On P.photo_id = A.photo_id
Where A.album_id = 20 OR A.album_id = 30

Dat is een andere query dan degene waarop ik "Dat kan niet" antwoordde. Dat was nl. deze:

SQL:

select count(distinct photo_id)
from zoph_photo_albums
where album_id in (20,30)

De reden dat deze niet "kan" is dat er ook zoekvelden kunnen zijn uit andere tabellen dan zoph_photo_albums.

Overigens heb ik m'n code veranderd en bouwt hij nu een query op met de JOIN ... ON syntax en dat gaat als een trein.

Bedankt iedereen!

[ Voor 4% gewijzigd door Verwijderd op 21-01-2006 21:22 ]