GNU Info

(mysql.info)Searching on two keys

---

Next: Calculating days Prev: example-Foreign keys Up: Examples

---

Enter node , (file) or (file)node

Searching on Two Keys
---------------------

MySQL doesn't yet optimize when you search on two different keys
combined with `OR' (Searching on one key with different `OR' parts is
optimized quite good):

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

The above way to solve this query is in effect an `UNION' of two
queries.