[MySQL]Table-Join op zichzelf mgv IF() lukt niet met indices

[marty]

Tabel in kwestie heeft de volgende (relevante) velden:
- ID
- ParentID
- MasterID

Ik probeer een JOIN tot stand te brengen van een tabel opzichzelf waarbij de volgende condities zijn:
- als er een MasterID is, doe de JOIN dan tussen dat MasterID en de ID, zo niet dan gewoon tussen de ID en ID
Dit doe ik met de volgende code:

MySQL:

T1.ID=IF(T2.MasterID IS NOT NULL, T2.MasterID, T2.ID)

Dit werkt in princiepe prima, alleen is het rete-traag omdat ik het niet voor elkaar krijg MySQL duidelijk te maken dat het een index moet gebruiken. Zowel ID als MasterID zijn geïndexeerd, maar mysql vindt het toch nodig alle ca 30.000 records af te gaan (bleek uit een EXPLAIN)

iemand enig idee hoe ik dit zo kan herschrijven zodat mysql niet alle records af gaat maar alleen de relevante? (en dus gebruik maakt van de indexen die er op staan)

[curry684]

MySQL kan exact 1 index per gejoinde tabel gebruiken, en ik kan me rustig voorstellen dat dat baggerproggel dat as usual weer eens niet snapt als je een functie als IF(...) over het veld gooit. Je zou een combined index kunnen proberen op (ID, MasterID) maar lijkt me sterk dat je dit opgelost gaat krijgen. MySQL is gewoon niet ontwikkeld voor complexere queries dan simpel wat tabelletjes straight joinen

[marty]

Combined index helpt idd weinig. Hij zegt dan wel heel stoer (onder Extra) dat ie een index gebruikt, maar denkt vervolgens nog steeds dat ie alle records af moet gaan ... lijkt het niet echt te snappen dus.
(toch even getest, maar leverde dus uiteindelijk ook geen snelheidswinst op - hij doet er dus niets mee)

[_js_]

MySQL gaat inderdaad geen index gebruiken wanneer je formules gebruikt op een veld.

Kun je in dit geval niet kiezen voor een left join (eventueel twee), en in het select gedeelte van je query een if te gebruiken om de juiste waarde terug te geven?

[marty]

Ik heb dit probleem nog even laten zitten voor wat het is, en ben nu tegen iets nieuws aangelopen, waarbij het weer mis gaat met de indici

ik heb deze simpele query:

MySQL:

explain SELECT ID FROM Table WHERE ID=1 OR MasterID=1

Wederom zegt MySQL alle rows in de tabel af te moeten gaan. Ik snap hier nix van.
Ik heb dit zowel op versie 3.23 getest als op 4.1.11
En ik heb het ook getest met alleen een index op ID en MasterID en met een gecombineerde index op beide kolommen. In het eerste geval zegt de explain dat er twee mogelijke indexen zijn, maar gebruikt ze niet, in het tweede geval ziet ie ook de gecombineerde index als mogelijkheid en gebruikt die ook, maar gaat vervolgens toch alle rows nog af

Iemand enig idee of dit nog valt te optimaliseren?

[marty]

bump

Niemand die weet hoe je mysql met een index om kan laten gaan icm een WHERE .. OR .. ???

[curry684]

Als je weet hoe een index werkt kun je zelf ook wel bedenken dat dit niet kan werken

Een index op (ID, MasterID) zal eerst geordend zijn op ID en vervolgens binnen identieke ID's op MasterID. Dat houdt dus in dat een match op MasterID zonder voorafgaande match op ID per definitie alsnog minimaal een full index scan oplevert, omdat ie de sortering van ID niet kan inzetten. Dit is dus alleen op te lossen met een DBMS dat per tabel meerdere indexen kan gebruiken.

Ik had al gezegd dat MySQL een baggerproggel is he?

[marty]

Als je weet hoe een index werkt kun je zelf ook wel bedenken dat dit niet kan werken

Ik weet het ongeveer

Een index op (ID, MasterID) zal eerst geordend zijn op ID en vervolgens binnen identieke ID's op MasterID. Dat houdt dus in dat een match op MasterID zonder voorafgaande match op ID per definitie alsnog minimaal een full index scan oplevert, omdat ie de sortering van ID niet kan inzetten. Dit is dus alleen op te lossen met een DBMS dat per tabel meerdere indexen kan gebruiken.

Aha, dat verklaart het eea....

Ik had al gezegd dat MySQL een baggerproggel is he?

ja

Ik ga het binnenkort eens met PostgreSQL proberen...


Voor nu denk ik dat ik het dan maar moet oplossen met een extra query na het inserten van ieder record, die de waarde van de zojuist aangemaakte PK aan MasterID toekent.... Dan verlies ik ook wat op performance, maar netto toch minder. En dan hoef ik alleen MasterID maar te controleren.

[ACM]

En als je een beetje creatief met MySQL bent?

SQL:

SELECT t.dingetjes, 
    CASE WHEN m.MasterID NOT IS NULL THEN m.iets ELSE p.iets END
FROM tabel t 
LEFT JOIN tabel m ON t.ID = m.MasterID
LEFT JOIN tabel p ON t.ID = p.ID

Postgres kan dit vanaf 8.1 (nu in stabiele beta2) vrij goed, als je het een beetje gunstig weet te joinen (niet met een functie). Of sowieso al als je het met een functionele index uitdrukt.


Owja, probeer het ook eens zo :

SQL:

SELECT ...
FROM
tabel t
JOIN tabel2 m ON (m.MasterID IS NULL AND m.ID = t.ID) OR (m.MasterID = t.ID)

Uiteraard met een gecombineerde index op MasterID, ID. Volgens mij is dat equivalent met je eerdere query.

[ Voor 56% gewijzigd door ACM op 22-09-2005 16:18 ]