[thelist] SQL help -- replacing OR to use index

Judah McAuley judah at wiredotter.com
Tue Aug 13 12:40:00 CDT 2002


Erick Papadakis wrote:
>  p.s., if you reply, kindly CC me as well.
>
> hi, i have the following SQL query
>
>   select col1 from table1
>   where
>   col2 = 'xxx' or col3 = 'xxx'
>
> how can i get rid of this OR? due to my table design and the logic
> involved, i have to check both these 2 columns (in a search function). i
> cannot concaneate them to make a third column because i need to check for
> exact match but for both the columns.

You most likely can't get rid of the OR statement and you should have
to.  OR is a basic boolean operator and is fundamental to SQL.  There
isn't any shame in using it.
>
> the EXPLAIN in mysql gives me the "ALL" type, which of course is not
> optimal. if i remove the OR in the above SQL, and make only one
> condition, then this EXPLAIN comes back as "const". this is ideal! and
> what i want.

The most ideal query would be something like SELECT '1' but that isn't
very useful is it?  No table scans, no boolean logic, nice simple plan.
  But the point is that if it doesn't get you what you want, it doesn't
matter how efficient the query is.  If you've got solid database design
then you shouldn't have to worry about using simple operators like OR
and AND.  The SQL engine is optimized to handle these sorts of
statements and should perform quite well.

> so what can i do to replace the OR? in a curious fit i tried :
>
>   select col1 from table1
>   where
>   'xxx' in (col2, col3)
>
> and to my surprise this works!!! but the EXPLAIN returns "ALL" again. so
> it is useless..

This is essentially the same WHERE statement just written with different
notation.  It gets parsed out to WHERE col2 = 'xxx' OR col3 = 'xxx'.

One thing that will speed up your query is to make sure that you have an
index on both col2 and col3.  If you have an index, then the engine will
scan the index first, find the proper row(s) and directly return those
rows.  This descreases the amount of time spent in the table itself.
Good index use and database tuning will help this situation a lot more
than trying to get rid of the OR in your SQL statement.

Hope this helps,
Judah




More information about the thelist mailing list