Alex Rivera | Logout

MySQL: how to index an "OR" clause

Asked 2010-05-13T19:09:47.403
31

I'm executing the following query

SELECT COUNT(*)
FROM table
WHERE field1='value' AND (field2 >= 1000 OR field3 >= 2000)

There is one index over field1 and another composited over field2&field3.

I see MySQL always selects the field1 index and then makes a join using the other two fields which is quite bad because it needs to join 146.000 rows.

Suggestions on how to improve this? Thanks

(EDIT AFTER TRYING SOLUTION PROPOSED)

Based in the solution proposed I've seen this on Mysql when playing with this.

SELECT COUNT(*) FROM (SELECT * FROM table WHERE columnA = value1
UNION SELECT * FROM table WHERE columnB = value2) AS unionTable;

is a lot slower than execute:

SELECT COUNT(*)
FROM table
WHERE (columnA = value1 AND columnB = value2)
      OR (columnA = value1 AND columnC = value3)

Having two composited index:

index1 (columnA,columnB)
index2 (columnA,columnC)

Interesting enough is that asking Mysql to "explain" the query it's taking always index1 on both cases and index2 is not used.

If I change the indexes to:

index1 (columnB,columnA)
index2 (columnC,columnA)

And the query to:

SELECT COUNT(*)
FROM table
WHERE (columnB = value2 AND columnA = value1)
      OR (columnC = value3 AND columnA = value1)

Then it's the fastest way I've found Mysql works.

Edit
Report

1 Answer

8

I'm new here, so I can't comment on other people's posts, but this is related to the posts by David M. and soulmerge.

The temporary table is not necessary. The UNION David M. suggested does not double count, as UNION implies a distinct (i.e. if a row exists in one half of the union, ignore it in the other). If you used UNION ALL, you would get two records.

The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements.

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

answered 2010-05-13T20:13:12.890

Your Answer