title: Mysql query optimisation - range searches
body: <p>Looking to find out some ideas on how to optimise this query - its pretty awkward with several range searches</p>
  
  <pre><code>SELECT t1.id, t1.custom_track_id, t1.deleted, t1.last_modified, t1.client_id
  FROM tracks t1
  INNER JOIN tracks t2 ON t1.custom_track_id = t2.custom_track_id
  AND t1.last_modified &gt; t2.last_modified
  AND t1.deleted !=0
  AND t2.deleted =0
  AND t2.client_id
  IN ( 103, 115, 116 ) 
  WHERE t1.client_id
  IN ( 103, 115, 116 ) 
  </code></pre>
  
  <p>All the fields its looking to find and joining on are INT fields. </p>
  
  <p>Indexes (yes theres a few dodgy ones in there) : </p>
  
  <pre><code>+--------+------------+------------------------------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | Table  | Non_unique | Key_name                                       | Seq_in_index | Column_name                | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  +--------+------------+------------------------------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | tracks |          0 | PRIMARY                                        |            1 | id                         | A         |      566045 |     NULL | NULL   |      | BTREE      |         |               |
  | tracks |          1 | client_id                                      |            1 | client_id                  | A         |         589 |     NULL | NULL   |      | BTREE      |         |               |
  | tracks |          1 | featured                                       |            1 | featured                   | A         |          16 |     NULL | NULL   |      | BTREE      |         |               |
  | tracks |          1 | system_status                                  |            1 | system_status              | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
  | tracks |          1 | created_by                                     |            1 | created_by                 | A         |         225 |     NULL | NULL   |      | BTREE      |         |               |
  | tracks |          1 | custom_track_id                                |            1 | custom_track_id            | A         |      283022 |     NULL | NULL   |      | BTREE      |         |               |
  | tracks |          1 | custom_track_id                                |            2 | custom_artist_id           | A         |      283022 |     NULL | NULL   |      | BTREE      |         |               |
  | tracks |          1 | counterpoint_id                                |            1 | counterpoint_id            | A         |      113209 |     NULL | NULL   | YES  | BTREE      |         |               |
  | tracks |          1 | system_status_2                                |            1 | system_status              | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
  | tracks |          1 | composition                                    |            1 | composition                | A         |          13 |     NULL | NULL   | YES  | BTREE      |         |               |
  | tracks |          1 | published_start_date                           |            1 | published_start_date       | A         |          13 |     NULL | NULL   | YES  | BTREE      |         |               |
  | tracks |          1 | published_end_date                             |            1 | published_end_date         | A         |          13 |     NULL | NULL   | YES  | BTREE      |         |               |
  | tracks |          1 | deleted                                        |            1 | deleted                    | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
  | tracks |          1 | restricted_access_required                     |            1 | restricted_access_required | A         |          13 |     NULL | NULL   |      | BTREE      |         |               |
  | tracks |          1 | mv_clientid_deleted_featured_restrictedaccess  |            1 | client_id                  | A         |         336 |     NULL | NULL   |      | BTREE      |         |               |
  | tracks |          1 | mv_clientid_deleted_featured_restrictedaccess  |            2 | custom_track_id            | A         |         336 |     NULL | NULL   |      | BTREE      |         |               |
  | tracks |          1 | mv_clientid_deleted_featured_restrictedaccess  |            3 | deleted                    | A         |         336 |     NULL | NULL   |      | BTREE      |         |               |
  | tracks |          1 | mv_clientid_deleted_featured_restrictedaccess  |            4 | last_modified              | A         |         336 |     NULL | NULL   |      | BTREE      |         |               |
  | tracks |          1 | mv_clientid_customtrackid_deleted_lastmodified |            1 | client_id                  | A         |         336 |     NULL | NULL   |      | BTREE      |         |               |
  | tracks |          1 | mv_clientid_customtrackid_deleted_lastmodified |            2 | custom_track_id            | A         |         336 |     NULL | NULL   |      | BTREE      |         |               |
  | tracks |          1 | mv_clientid_customtrackid_deleted_lastmodified |            3 | deleted                    | A         |         336 |     NULL | NULL   |      | BTREE      |         |               |
  | tracks |          1 | mv_clientid_customtrackid_deleted_lastmodified |            4 | last_modified              | A         |         336 |     NULL | NULL   |      | BTREE      |         |               |
  +--------+------------+------------------------------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  </code></pre>
  
  <p>And the EXPLAIN : </p>
  
  <pre><code>---+---------+-------------------------------------------------+-------+--------------------------+
  | id | select_type | table | type  | possible_keys                                                                                                                  | key                                           | key_len | ref                                             | rows  | Extra                    |
  +----+-------------+-------+-------+--------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------+---------+-------------------------------------------------+-------+--------------------------+
  |  1 | SIMPLE      | t1    | range | client_id,custom_track_id,deleted,mv_clientid_deleted_featured_restrictedaccess,mv_clientid_customtrackid_deleted_lastmodified | mv_clientid_deleted_featured_restrictedaccess | 4       | NULL                                            | 16018 | Using where; Using index |
  |  1 | SIMPLE      | t2    | ref   | client_id,custom_track_id,deleted,mv_clientid_deleted_featured_restrictedaccess,mv_clientid_customtrackid_deleted_lastmodified | custom_track_id                               | 302     | synchtank_shared_application.t1.custom_track_id |     2 | Using where              |
  +----+-------------+-------+-------+--------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------+---------+-------------------------------------------------+-------+--------------------------+
  </code></pre>
  
  <p>So looking for ways to either optimise the query or the indexes. Im also curious why of the 2 composite indexes it could match on, it chooses the one without last_modified in it. </p>
  
  <p>EXPLAIN for Stephan : </p>
  
  <pre><code>+----+-------------+-------+-------+-----------------------------------------------+-----------------------------------------------+---------+------+-------+---------------------------------------------+
  | id | select_type | table | type  | possible_keys                                 | key                                           | key_len | ref  | rows  | Extra                                       |
  +----+-------------+-------+-------+-----------------------------------------------+-----------------------------------------------+---------+------+-------+---------------------------------------------+
  |  1 | SIMPLE      | t1    | range | client_id                                     | client_id                                     | 4       | NULL |  8111 | Using where                                 |
  |  1 | SIMPLE      | t2    | range | mv_clientid_deleted_featured_restrictedaccess | mv_clientid_deleted_featured_restrictedaccess | 4       | NULL | 16018 | Using where; Using index; Using join buffer |
  +----+-------------+-------+-------+-----------------------------------------------+-----------------------------------------------+---------+------+-------+---------------------------------------------+
  </code></pre>