title: Mysql improve query speed involving multiple tables
body: <p>I have the following query</p>
  
  <pre><code>SELECT a.id, b.id from table1 AS a, table2 AS b
  WHERE a.table2_id IS NULL
  AND a.plane = SUBSTRING(b.imb, 1, 20)
  AND (a.stat LIKE "f%" OR a.stat LIKE "F%")
  </code></pre>
  
  <p>Here is the output of EXPLAIN</p>
  
  <pre>+----+-------------+-------+------+-------------------------------------------------------------------------------------------+------------------------------+---------+------+----------+-------------+
  | id | select_type | table | type | possible_keys                                                                             | key                          | key_len | ref  | rows     | Extra       |
  +----+-------------+-------+------+-------------------------------------------------------------------------------------------+------------------------------+---------+------+----------+-------------+
  |  1 | SIMPLE      | b     | ALL  | NULL                                                                                      | NULL                         | NULL    | NULL | 28578039 |             |
  |  1 | SIMPLE      | a     | ref  | index_on_plane,index_on_table2_id_id,mysql_confirmstat_on_stat                            | index_on_plane               | 258     |  func|        2 | Using where |
  +----+-------------+-------+------+-------------------------------------------------------------------------------------------+------------------------------+---------+------+----------+-------------+
  </pre>
  
  <p>The query takes around <strong>80 minutes</strong> to execute.</p>
  
  <p>The indexes on table1 are as follows</p>
  
  <pre>
  +--------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | Table        | Non_unique | Key_name                       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  +--------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | table1 |          0 | PRIMARY             |            1 | id         | A         |    50319117 |     NULL | NULL   |      | BTREE      |         |               |
  | table1 |          1 | index_on_post       |            1 | post       | A         |     7188445 |     NULL | NULL   | YES  | BTREE      |         |               |
  | table1 |          1 | index_on_plane      |            1 | plane      | A         |    25159558 |     NULL | NULL   | YES  | BTREE      |         |               |
  | table1 |          1 | index_on_table2_id  |            1 | table2_id  | A         |    25159558 |     NULL | NULL   | YES  | BTREE      |         |               |
  | table1 |          1 | index_on_stat       |            1 | stat       | A         |         187 |     NULL | NULL   | YES  | BTREE      |         |               |
  +--------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  </pre>
  
  <p>and table2 indexes are.</p>
  
  <pre>
  +-------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | Table | Non_unique | Key_name                  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  +-------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | table2 |          0 | PRIMARY                 |            1 | id             | A         |    28578039 |     NULL | NULL   |      | BTREE      |         |               |
  | table2 |          1 | index_on_post           |            1 | post           | A         |    28578039 |     NULL | NULL   | YES  | BTREE      |         |               |
  | table2 |          1 | index_on_ver            |            1 | ver            | A         |        1371 |     NULL | NULL   | YES  | BTREE      |         |               |
  | table2 |          1 | index_on_imb            |            1 | imb            | A         |    28578039 |     NULL | NULL   | YES  | BTREE      |         |               |
  +-------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  </pre>
  
  <p>How can the execution time of this query be improved?</p>
  
  <p>Here is the updated explain</p>
  
  <pre><code>EXPLAIN SELECT STRAIGHT_JOIN a.id, b.id from table1 AS a JOIN b AS b  
  ON a.plane=substring(b.imb,1,20) 
  WHERE a.table2_id IS NULL  
  AND (a.stat LIKE "f%" OR a.stat LIKE "F%");
  </code></pre>
  
  <pre>
  +----+-------------+-------+------+-------------------------------------------------------------------------------------------+-------------------------------+---------+-------+----------+--------------------------------+
  | id | select_type | table | type | possible_keys                                       | key                | key_len | ref   | rows     | Extra                          |
  +----+-------------+-------+------+-------------------------------------------------------------------------------------------+-------------------------------+---------+-------+----------+--------------------------------+
  |  1 | SIMPLE      | a     | ref  | index_on_plane,index_on_table2_id,index_on_stat     | index_on_table2_id | 5       | const |   500543 | Using where                    |
  |  1 | SIMPLE      | b     | ALL  | NULL                                                | NULL               | NULL    | NULL  | 28578039 | Using where; Using join buffer |
  +----+-------------+-------+------+-------------------------------------------------------------------------------------------+-------------------------------+---------+-------+----------+--------------------------------+
  </pre>
  
  <p>SQL fiddle link <a href="http://www.sqlfiddle.com/#!2/362a6/4" rel="nofollow">http://www.sqlfiddle.com/#!2/362a6/4</a></p>