MySQL :: MySQL 5.6 リファレンスマニュアル :: 3.6.7 2 つのキーを使用した検索

8.0 English
5.7 English
5.6 English
5.5 English

MySQL 5.6 リファレンスマニュアル / ... / 2 つのキーを使用した検索

3.6.7 2 つのキーを使用した検索

1 つのキーを使用した OR の処理は、AND の処理と同様にかなり最適化されています。

注意が必要なのは、OR で結合された 2 つの異なるキーを使用して検索する場合です。

SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR  field2_index = '1'

この場合は最適化されています。セクション8.2.1.4「インデックスマージの最適化」を参照してください。

2 つの異なる SELECT ステートメントの出力を結合する UNION を使用することでも、この問題を効率的に解決できます。セクション13.2.9.4「UNION 構文」を参照してください。

各 SELECT は 1 つのキーだけを検索するため、最適化できます。

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';

PREV HOME UP NEXT

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.