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'

In MySQL 4.0 and up, you can solve the problem efficiently by using a UNION that combines the output of two separate SELECT statements. See Section 12.2.7.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';

Prior to MySQL 4.0, you can achieve the same effect by using a TEMPORARY table and separate SELECT statements. This type of optimization is also very good if you are using very complicated queries where the SQL server does the optimizations in the wrong order. 

CREATE TEMPORARY TABLE tmp
SELECT field1_index, field2_index
    FROM test_table WHERE field1_index = '1';
INSERT INTO tmp
SELECT field1_index, field2_index
    FROM test_table WHERE field2_index = '1';
SELECT * from tmp;
DROP TABLE tmp;

This method of solving the problem is in effect a UNION of two queries.


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.