DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(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