Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.0Mb
PDF (A4) - 35.1Mb
PDF (RPM) - 34.1Mb
EPUB - 8.6Mb
HTML Download (TGZ) - 8.3Mb
HTML Download (Zip) - 8.4Mb
HTML Download (RPM) - 7.2Mb
Eclipse Doc Plugin (TGZ) - 9.2Mb
Eclipse Doc Plugin (Zip) - 11.3Mb
Man Pages (TGZ) - 200.1Kb
Man Pages (Zip) - 305.3Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Searching on Two Keys

4.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. See Section 9.2.1.4, “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 14.2.9.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
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
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.
Sign Up Login You must be logged in to post a comment.