[MySQL] index t.b.v.: "col1 OR col2"

[Verwijderd]

Wij nemen een InnoDB tabel 'demo':

code:

id, owner_id, group_id, demo1, demo2, etc...

Wij plaatsen een index op:

code:

owner_id, group_id

Wij runnen de volgende query:

SQL:

SELECT id, demo1, demo2
FROM demo
WHERE owner_id = 1 OR group_id = 2

MySQL (en PostgreSQL overigens ook niet; zojuist geprobeerd) is dan niet in staat om de index te gebruiken. Een EXPLAIN laat dan zien dat er een full table scan wordt uitgevoerd, overeenkomstig de MySQL documentatie:

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

Any index that does not span all AND levels in the WHERE clause is not used to optimize the query. In other words, to be able to use an index, a prefix of the index must be used in every AND group.

Kortom; de owner_id en group_id staan in een andere AND-group, waardoor de index niet wordt gebruikt.

Zijn hier work arounds / oplossingen voor?

[Zoijar]

Twee apparte indices op owner_id en group_id?

[.oisyn]

Vrij logisch, er zijn feitelijk twee verschillende indexen nodig. Ik zou niet zo moeilijk doen en gewoon een index maken op owner_id en een index op group_id, en dan twee aparte queries doen (evt. met een UNION [DISTINCT])

[Verwijderd]

De tabel bevat > 100.000.000.000 records.

"owner_id OR group_id" zal de resultset beperken tot gemiddeld > 250.000.000 records.

[ Voor 6% gewijzigd door Verwijderd op 30-05-2012 13:20 ]

[NMe]

Dat is niet relevant. Je hebt een gecombineerde index die je vervolgens niet kan gebruiken om de reden die hierboven uitgelegd wordt. De makkelijkste oplossing is inderdaad gewoon domweg een extra index aanleggen op het tweede veld en eventueel de bestaande index beperken tot het andere veld (al is dat volgens mij niet per se nodig).

[.oisyn]

(al is dat volgens mij niet per se nodig)

Klopt, maar het is natuurlijk een beetje onzinnig om een tweedimensionale index te gaan zitten gebruiken als je toch nooit gaat zoeken in die tweede dimensie. Daarmee maak je je index alleen maar groter en je inserts duurder

[ Voor 5% gewijzigd door .oisyn op 30-05-2012 13:30 ]

[NMe]

.oisyn schreef op woensdag 30 mei 2012 @ 13:29:
[...]

Klopt, maar het is natuurlijk een beetje onzinnig om een tweedimensionale index te gaan zitten gebruiken als je toch nooit gaat zoeken in die tweede dimensie. Daarmee maak je je index alleen maar groter en je inserts duurder

Hangt er een beetje vanaf wat je verder voor query's doet en hoe groot de load op de database is natuurlijk. Zo'n samengestelde index heeft natuurlijk wel wat snelheidsvoordelen als je hem in een situatie gebruikt waar hij wel op zijn plek is. Voor de meeste gevallen heb je inderdaad gelijk en zou ik het waarschijnlijk op twee enkelvoudige indices houden in dit geval.

[.oisyn]

Klopt, maar het is natuurlijk een beetje onzinnig om een tweedimensionale index te gaan zitten gebruiken als je toch nooit gaat zoeken in die tweede dimensie

Inderdaad, als je dat wel doet dan is het wellicht wel zinnig, maar daar had ik het niet over

[NMe]

Dat weet ik, maar juist daarom maakte ik die nuance wel.

[.oisyn]

Overigens, als je unieke entries terugwil uit de query en UNION DISTINCT is te duur omdat er zoveel resultaten zijn, dan zou je het ook zo kunnen doen:

SQL:

SELECT id, demo1, demo2 
FROM demo 
WHERE owner_id = 1 AND group_id != 2

UNION ALL SELECT id, demo1, demo2
FROM demo
WHERE group_id = 2

Voor de eerste helft van de query heb je dan weldegelijk een index op beide kolommen nodig. Voor de tweede helft slechts een index op group_id.

[JaQ]

NMe schreef op woensdag 30 mei 2012 @ 13:23:
Dat is niet relevant.

Wel relevant, maar om een andere reden

(de data is selectief genoeg, want hij selecteert 0,25 procent van de data.. Een bruikbare index zou gebruikt moeten worden indien beschikbaar).

---

De door . oisyn genoemde union is m.i. de enige oplossing, logisch is het volledig correct dat een or conditie geen gebruik kan maken van een index. Union distinct ken ik overigens niet, volgens de sql specificatie is een union bij default distinct.

[NMe]

Distinct als keyword toevoegen heeft bij mijn weten geen ander effect dan expliciet aangeven dat je echt een distinct resultaat wil, net zoals je een join ook expliciet een inner join mag noemen.

[.oisyn]

Dat klopt idd, my bad. Heb mijn code aangepast zodat hij een UNION ALL doet.

[Verwijderd]

.oisyn schreef op woensdag 30 mei 2012 @ 17:15:

SQL:

SELECT id, demo1, demo2 
FROM demo 
WHERE owner_id = 1 AND group_id != 2

UNION ALL SELECT id, demo1, demo2
FROM demo
WHERE group_id = 2

Nice!