An OR
using a single key is well
optimized, as is the handling of
AND
.
The one tricky case is that of searching on two different keys
combined with OR
:
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' OR field2_index = '1'
This case is optimized. See Section 7.2.6, “Index Merge Optimization”.
You can also solve the problem efficiently by using a
UNION
that combines the output of
two separate SELECT
statements.
See Section 12.2.8.3, “UNION
Syntax”.
Each SELECT
searches only one key
and can be optimized:
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' UNION SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';
User Comments
One thing to remember when using the 'union' statement (as I found out): the resulting set removes all duplicate entries unless you proceed the 'union' statement with the word 'all'. Assuming the table:
11 rows in set (0.00 sec)mysql> select * from ourpets;
The result when you use the 'or' statement:
mysql> select sex,owner from ourpets where sex='m' or owner='david';
7 rows in set (0.00 sec)
here is the result when the 'union' statement is used:
mysql> select sex,owner from ourpets where sex='m' union select sex,owner from ourpets where owner='david';
5 rows in set (0.00 sec)
I expected 7 rows to be returned; however, only 5 rows are returned because all duplicate rows are removed.
here is the result when the 'all' keyword is used after the 'union' statement:
mysql> select sex,owner from ourpets where sex='m' union all select sex,owner from ourpets where owner='david';
7 rows in set (0.00 sec)
If you use the TEMPORARY trick and you want to get the same result as
SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR field2_index = '1'
you need to write
SELECT DISTINCT * from tmp;
instead of
SELECT * from tmp;
as written in the doc.
Add your own comment.