Dotaz: MySQL4 optimalizace dotazu s OR

[Jan Smrz]

Zdravim, resim jeden problem a to je pouziti indexu pri OR v dotazu.

Mejme tabulku t_tabulka a v ni krom jineho jsou sloupce hodnota1 a hodnota2, na sloupcich jsou indexy (Btree)

Pokud udelam nasledujici: SELECT * FROM t_tabulka WHERE hodnota1 = 1 AND hodnota2 = 1 indexy se spravne pouziji a dotaz se provede rychle...

Ovsem pokud pouziji: SELECT * FROM t_tabulka WHERE hodnota1 = 1 OR hodnota2 = 1 tak k pouziti indexu nedojde presto ze to po MySQL pozaduji pres USE INDEX()

Co delam spatne nebo co jsem na MySQL nepochopil? Muze toto nekdo osvetlit proc pri AND dochazi k pouziti indexu a pri OR ne?

---

---

Odpovědi

[fraxinus]

Index sa s OR nepouzije nikdy lebo sa to neda (Index sa pouzije len ked sa nim vyberie male mnozstvo dat, OR pri nesplneni podmienky ti vrati vzdy vsetky vety, takze nema zmysel pouzit index). Mozes pouzit IN kde je ista sanca ze to pojde cez index (zalezi na implementacii DB). Inak treba pouzit union:

select * from t_tabulka where hodnota1=1 union select * from t_tabulka where hodnota2=2

Tento union je identicky s tvojim selektom, v podstate len riesi leaky abstraction SQL implementacie.

[krtek007]

Oprava:

select * from t_tabulka where hodnota1=1 union select * from t_tabulka where hodnota2=1

Teraz som si vsimol ze v tvojom pripade IN nemozes pouzit, len ak by si mal toto:

select * from tabulka where hodnota=1 or hodnota=2

bude

select * from tabulka where hodnota in (1,2)

alebo:

select * from tabulka where hodnota=1 union select * from tabulka where hodnota=2

[krtek007]

OR a AND jsou dvě naprosto rozdílné operace. AND zužuje výslednou množinu, OR ji rozšiřuje. Čím máte větší výslednou množinu, tím menší smysl má použití indexu. Řada db s operátorem OR nemá problém, některé vyžadují složený index - jak je to u MySQL nevím. Jistým způsobem podobná omezení lze obejít

SELECT * FROM tabulka WHERE hodnota1 = c1
UNION
SELECT * FROM tabulka WHERE hodnota2 = c2

[Josef Kufner]

Nutno dodat, že ten union nemusí být nutně efektivnější než neoptimalizovaný OR.

[Josef Kufner]

Zkoušel jsi ten dotaz postavit nějak úplně jinak, aby ses tomu oru vyhnul? Třeba jinak postavit joiny a podobně...

[Tomáš]

Další možnost pro OR operaci je použití bitmap indexů. MySQL pokud vím nemá přímo bitmap indexy ale má index merge. Podívejte se odkaz.

Pro AND podmínku (s rovnostmi) ideálně potřebujete složený index z polí (hodnota1, hodnota2). Alternativně je možno mít i dva samostatné indexy a nechat optimalizátor ať si podle selektivity vybere ten vhodnější.

Pro OR podmínku potřebuje dva samostatné indexy a doufat, že se optimalizátor rozhodne pro zmíněný index_merge. Posbírejte statistiky nad těmi sloupci (a indexy pokud to MySQL umí) a doufejte. Pokud bude selektivita malá tj. aplikací jednoduché podmínky (operandu OR) odpadne nejvýše 60% záznamů, tak bych hádal že i tak to skončí na full table scan (= bez použití indexu).