Skip navigation links
3.6.8 Calculating Visits Per Day »
Section Navigation      [Toggle]

3.6.7. Searching on Two Keys

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 from MySQL 5.0.0. 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

Posted by David Thompson on March 9 2005 3:01pm[Delete] [Edit]

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:

mysql> select * from ourpets;

+----------+--------+------------+------+------------+------------+
| name     | owner  | species    | sex  | birth      | death      |
+----------+--------+------------+------+------------+------------+
| Fluffy   | Harold | cat        | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat        | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog        | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog        | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog        | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird       | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird       | f    | 1997-12-09 | NULL       |
| Slim     | Benny  | snake      | m    | 1996-04-29 | NULL       |
| Dalli    | Alli   | canine     | m    | 2001-12-20 | NULL       |
| Tara     | David  | canine     | f    | 2002-05-17 | NULL       |
| Mimi     | Alli   | guinea pig | m    | 2004-05-17 | NULL       |
+----------+--------+------------+------+------------+------------+
11 rows in set (0.00 sec)

The result when you use the 'or' statement:

mysql> select sex,owner from ourpets where sex='m' or owner='david';
+------+-------+
| sex  | owner |
+------+-------+
| m    | Gwen  |
| m    | Benny |
| m    | Diane |
| m    | Benny |
| m    | Alli  |
| f    | David |
| m    | Alli  |
+------+-------+
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';
+------+-------+
| sex  | owner |
+------+-------+
| m    | Gwen  |
| m    | Benny |
| m    | Diane |
| m    | Alli  |
| f    | 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';
+------+-------+
| sex  | owner |
+------+-------+
| m    | Gwen  |
| m    | Benny |
| m    | Diane |
| m    | Benny |
| m    | Alli  |
| m    | Alli  |
| f    | David |
+------+-------+
7 rows in set (0.00 sec)


Posted by T.F. Seak on July 31 2005 6:07pm[Delete] [Edit]

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.