OR Statement not using indexes

[Stasonis]

I have a SQL statement in MySQL:

SELECT comm FROM trd WHERE rr="111" OR second_rr="111"

Both the rr and second_rr columns are indexed, however this query is not making use of them The table has 94000 rows but only about 300 unique rr or second rr values. If I elminate the OR clause and just test for one of the values, ie:

SELECT comm FROM trd WHERE rr="111"

MySQL makes use of the indexes and returns the results quickly. I really need to make use of these indexes in the OR query because the data set is growing rapidly and I cannot afford for this query to be any slower then it already is (Currently takes about 3 seconds) as it is part of a much more complex web app. Any ideas on why the index is not being used, or any way to force MySQL to use it?

[Stasonis]

I should add that if I replace OR with AND the indexes are used and only 660 rows are searched. This of course gives me the wrong results but I would have assumed the AND and OR operators functioned similarly

[bwkaz]

<horrible hack workaround>

Use a union query perhaps?

You can split the query up into two (one with each WHERE clause), then union them together. Or actually, you could maybe just execute two separate queries, and combine them in the web app code.

</horrible hack workaround>

What version of MySQL? Have you tried the latest? It may be a bug in the version you're using.

Otherwise, does it help to make one index over both columns?

[jdoe]

Completely uneducated guess, but does it help to add a multiple-column index to include both columns?

[Stasonis]

Turns out OR does not make use of indexes. We're using MySQL 4.1 here, not ready to move to 5.0 yet since it's still alpha. The workaround was indeed to use a union statment:

SELECT comm FROM trd WHERE rr="111" OR rr2="111"

became

SELECT comm FROM trd WHERE rr="111"
UNION
SELECT comm FROM trd WHERE rr2="111"

I did not want to combine the results of these two queries within the web app because I was actually going to take a sum of the comm variable, reather then retrieve a list and wanted to avoid the data trasfer overhead of sending it back to my web app. One downside/advantage to the UNION is that it removes duplicates, which altered the behaviour of this query a little. Found a workaround, but it's something to keep in mind if anyone else is facing the same problem

[Stasonis]

In response to the multi-column index, it made no difference. After using the union statement however the query went from 3.5 seconds down to .2 seconds, well within an acceptable range for the app. Thanks all for your help

[bwkaz]

We're using MySQL 4.1 here, Which version of 4.1 though?

I've got 4.1.11 here, although I've never tried using OR on an indexed column (or two of them). They did fix some things in some "patchlevel" releases.

Of course, you're not still using 3.whatever, so that's good. :)

Anyway, if the union worked (or got you close enough so you could make it work), then good. :)