(mysql.info.gz) Searching on two keys
Info Catalog
(mysql.info.gz) example-Foreign keys
(mysql.info.gz) Examples
(mysql.info.gz) Calculating days
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. Index Merge
optimization.
In MySQL 4.0 and up, you can also solve the problem efficiently by
using a `UNION' that combines the output of two separate `SELECT'
statements. UNION.
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.
Info Catalog
(mysql.info.gz) example-Foreign keys
(mysql.info.gz) Examples
(mysql.info.gz) Calculating days
automatically generated byinfo2html