Slow performance with OR clause

Question

I have a MyISAM Table with circa 1 million rows.

SELECT * FROM table WHERE colA='ABC' AND (colB='123' OR colC='123');

The query above takes over 10 seconds to run. All columns in question are indexed.

But when I split it as follows...

SELECT * FROM table WHERE colA='ABC' AND colB='123';
SELECT * FROM table WHERE colA='ABC' AND colC='123';

Each individual query takes 0.002 seconds.

What gives, and how do I optimize the table/query?

Answer 1

( SELECT * FROM table WHERE colA='ABC' AND colB='123' )
UNION DISTINCT
( SELECT * FROM table WHERE colA='ABC' AND colC='123' )
;

And have

INDEX(colA, colB),
INDEX(colA, colC)

You should consider moving to InnoDB, though it may not matter to this particular question.

Here's how the UNION will work:

1. Perform each SELECT. They will be very efficient due to the indexes suggested.
2. Collect the results in a tmp table.
3. De-dup the temp table and deliver the resulting rows.

All the rows are 'touched' in the original query (with OR).

With the UNION:

1. Only the necessary rows are touched in the SELECTs.
2. Those rows are written to the tmp table.
3. Those rows are reread. (The de-dupping may involve touching the rows more than once.)