title: MySQL chooses completely wrong index
body: <p>For some reason, MySQL chooses completely wrong indexes. It feels like it doesn't check which index is the best for the query.</p>
  
  <p>Some indexes on the contacts table:</p>
  
  <pre><code>+----------+------------+---------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | Table    | Non_unique | Key_name                              | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  +----------+------------+---------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | contacts |          0 | PRIMARY                               |            1 | id               | A         |     2227424 |     NULL | NULL   |      | BTREE      |         |               |
  | contacts |          1 | idx_contacts_date_modfied             |            1 | date_modified    | A         |      261152 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_contacts_id_del                   |            1 | id               | A         |     2228229 |     NULL | NULL   |      | BTREE      |         |               |
  | contacts |          1 | idx_contacts_id_del                   |            2 | deleted          | A         |     2228229 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_contacts_date_entered             |            1 | date_entered     | A         |      286622 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_cont_last_first                   |            1 | last_name        | A         |      783981 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_cont_last_first                   |            2 | first_name       | A         |     1434526 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_cont_last_first                   |            3 | deleted          | A         |     1434526 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_contacts_del_last                 |            1 | deleted          | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_contacts_del_last                 |            2 | last_name        | A         |      830164 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_cont_del_reports                  |            1 | deleted          | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_cont_del_reports                  |            2 | reports_to_id    | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_cont_del_reports                  |            3 | last_name        | A         |      830164 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_reports_to_id                     |            1 | reports_to_id    | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_del_id_user                       |            1 | deleted          | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_del_id_user                       |            2 | id               | A         |     2228229 |     NULL | NULL   |      | BTREE      |         |               |
  | contacts |          1 | idx_del_id_user                       |            3 | assigned_user_id | A         |     2228229 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_cont_assigned                     |            1 | assigned_user_id | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_contact_title                     |            1 | title            | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_contact_mkto_id                   |            1 | mkto_id          | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_contacts_first_last               |            1 | first_name       | A         |      265736 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_contacts_first_last               |            2 | last_name        | A         |     1453136 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_contacts_first_last               |            3 | deleted          | A         |     1453136 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_contacts_assigned_del             |            1 | assigned_user_id | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_contacts_assigned_del             |            2 | deleted          | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_contacts_tmst_id                  |            1 | team_set_id      | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_contacts_tmst_id                  |            2 | deleted          | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_del_date_modified_id              |            1 | deleted          | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
  | contacts |          1 | idx_del_date_modified_id              |            2 | date_modified    | A         |      265687 |     NULL | NULL   | YES  | BTREE      |         |               |
  +----------+------------+---------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  </code></pre>
  
  <p>The query:</p>
  
  <pre><code>SELECT SQL_NO_CACHE contacts.id,
         contacts.date_modified contacts__date_modified
  FROM contacts
  INNER JOIN
    (SELECT tst.team_set_id
     FROM team_sets_teams tst
     INNER JOIN team_memberships team_membershipscontacts ON (team_membershipscontacts.team_id = tst.team_id)
     AND (team_membershipscontacts.user_id = '5daa2e92-c347-11e9-afc5-525400a80916')
     AND (team_membershipscontacts.deleted = 0)
     GROUP BY tst.team_set_id) contacts_tf ON contacts_tf.team_set_id = contacts.team_set_id
  LEFT JOIN contacts_cstm contacts_cstm ON contacts_cstm.id_c = contacts.id
  WHERE contacts.deleted = 0
  ORDER BY contacts.date_modified DESC,
           contacts.id DESC
  LIMIT 21;
  </code></pre>
  
  <p>For some reason, the compiler chooses index <code>idx_contacts_del_last</code> which contains field which is not even in the query! and the query takes around 2 minutes (2M rows).</p>
  
  <p>When I force <code>idx_contacts_date_modfied</code> or <code>idx_del_date_modified_id</code> index, the query takes 0.5s.</p>
  
  <p>For fun, I tried to delete the index <code>idx_contacts_del_last</code> and add it again. After that, mysql chose a DIFFERENT index - <code>idx_reports_to_id</code> implying that MySQL does not even try to choose optimal index and probably chooses the first index which it sees... From my observation the first index, which have field <code>deleted</code> as the first field and was added first is chosen.</p>
  
  <p>So I dropped and recreated all indexes except the one I want the query to use and it finally chooses the correct one. But now I need a different query to use a different index and it still keeps using the one I didn't recreate.</p>
  
  <p>Is there some setting which can make mysql look more thoroughly on index optimization? I Use mysql 5.7.6 </p>
  
  <p>EDIT:</p>
  
  <p><strong>The query is system generated and I can't alter it</strong></p>
  
  <p>Explain:</p>
  
  <pre><code>+----+-------------+--------------------------+------------+--------+--------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------------------------------------+---------+----------+---------------------------------------------------------------------+
  | id | select_type | table                    | partitions | type   | possible_keys                                                                                                                  | key                        | key_len | ref                                       | rows    | filtered | Extra                                                               |
  +----+-------------+--------------------------+------------+--------+--------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------------------------------------+---------+----------+---------------------------------------------------------------------+
  |  1 | PRIMARY     | contacts                 | NULL       | ref    | idx_contacts_del_last,idx_cont_del_reports,idx_del_id_user,idx_contacts_tmst_id,idx_del_date_modified,idx_del_date_modified_id | idx_contacts_del_last      | 2       | const                                     | 1114111 |   100.00 | Using temporary; Using filesort                                     |
  |  1 | PRIMARY     | &lt;derived2&gt;               | NULL       | ALL    | NULL                                                                                                                           | NULL                       | NULL    | NULL                                      |       2 |    50.00 | Using where; Using join buffer (Block Nested Loop)                  |
  |  1 | PRIMARY     | contacts_cstm            | NULL       | eq_ref | PRIMARY                                                                                                                        | PRIMARY                    | 144     | sugarcrm.contacts.id                      |       1 |   100.00 | Using index                                                         |
  |  2 | DERIVED     | team_membershipscontacts | NULL       | ref    | idx_team_membership,idx_teammemb_team_user,idx_del_team_user                                                                   | idx_team_membership        | 145     | const                                     |       2 |    99.36 | Using index condition; Using where; Using temporary; Using filesort |
  |  2 | DERIVED     | tst                      | NULL       | ref    | idx_ud_set_id,idx_ud_team_id,idx_ud_team_set_id,idx_ud_team_id_team_set_id                                                     | idx_ud_team_id_team_set_id | 144     | sugarcrm.team_membershipscontacts.team_id |       1 |   100.00 | Using index                                                         |
  +----+-------------+--------------------------+------------+--------+--------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------------------------------------+---------+----------+---------------------------------------------------------------------+
  </code></pre>