3.5.7 Searching on Two Keys

MySQL doesn't yet optimise when you search on two different keys combined with OR (searching on one key with different OR parts is optimised quite well):

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

The reason is that we haven't yet had time to come up with an efficient way to handle this in the general case. (The AND handling is, in comparison, now completely general and works very well.)

For the moment you can solve this very efficiently by using a TEMPORARY table. This type of optimisation is also very good if you are using very complicated queries where the SQL server does the optimisations 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;

The above way to solve this query is in effect a UNION of two queries. See section 6.4.1.2 UNION Syntax.

User Comments

Add your own comment.