title: Why isn't it using the index?
body: <p>Hello kind people of the internet.</p>
  <p>I am wrecking my head trying to figure out why the optimiser isn't using my index for my query on Amazon Aurora. The query is dynamically created based on a report users have created through an applications UI, so I can't change the query per se.</p>
  <p>The query uses these qualifiers</p>
  <pre><code>WHERE
    table_in_question.deleted = 0
  ORDER BY
    table_in_question.date_modified DESC,
    table_in_question.id DESC
  </code></pre>
  <p>I have an index, &quot;my_index&quot;, which indexes these specific fields (table_in_question fields deleted, date_modified, ID) but MySQL doesn't use it.</p>
  <p>The query takes approx 1200 ms to run. If I add FORCE INDEX (my_index) it takes about 120ms. Arguably about 10x faster - but unless I use force index, it doesn't use it.</p>
  <p>Around 1 million rows are returned according to EXPLAIN, so I don't think it's a case of not using the index because of a low amount of rows being returned is the case.</p>
  <p>The full query is</p>
  <pre><code>SELECT
    case when some_table.id IS NOT NULL then some_table.id else &quot;&quot; end my_favorite,
    table_in_question.date_entered,
    table_in_question.name,
    table_in_question.description,
    table_in_question.pr_is_read,
    table_in_question.pr_is_approved,
    table_in_question.parent_type,
    table_in_question.parent_id,
    table_in_question.id,
    table_in_question.date_modified,
    table_in_question.assigned_user_id,
    table_in_question.created_by
  FROM
    table_in_question
    INNER JOIN (
      SELECT
        tst.team_user_is_member_of
      FROM
        team_sets_teams tst
        INNER JOIN team_memberships team_membershipstable_in_question ON (
          team_membershipstable_in_question.team_id = tst.team_id
        )
        AND (team_membershipstable_in_question.user_id = 'UUID')
        AND (team_membershipstable_in_question.deleted = 0)
      GROUP BY
        tst.team_user_is_member_of
    ) table_in_question_tf ON table_in_question_tf.team_user_is_member_of = table_in_question.team_user_is_member_of
    LEFT JOIN systemfavourites sf_table_in_question ON (sf_table_in_question.module = 'table_in_question')
    AND (sf_table_in_question.record_id = table_in_question.id)
    AND (sf_table_in_question.assigned_user_id = 'UUID')
    AND (sf_table_in_question.deleted = '0')
    INNER JOIN opportunities jt1_table_in_question ON (table_in_question.opportunity_id = jt1_table_in_question.id)
    AND (jt1_table_in_question.deleted = 0)
    LEFT JOIN another_table jt1_table_in_question_cstm ON jt1_table_in_question_cstm.id_c = jt1_table_in_question.id
    LEFT JOIN systemfavourites table_in_question_favorite ON (table_in_question.id = table_in_question_favorite.record_id)
    AND (table_in_question_favorite.deleted = '0')
    AND (table_in_question_favorite.module = 'table_in_question')
    AND (table_in_question_favorite.created_by = 'UUID')
    LEFT JOIN users some_table ON (
      some_table.id = table_in_question_favorite.modified_user_id
    )
    AND (some_table.deleted = 0)
  WHERE
    table_in_question.deleted = 0
  ORDER BY
    table_in_question.date_modified DESC,
    table_in_question.id DESC
  ;
  </code></pre>
  <p>EXPLAIN shows this</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>select_type</th>
  <th>table</th>
  <th>partitions</th>
  <th>type</th>
  <th>possible_keys</th>
  <th>key</th>
  <th>key_len</th>
  <th>ref</th>
  <th>rows</th>
  <th>filtered</th>
  <th>Extra</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>PRIMARY</td>
  <td>table_in_question</td>
  <td></td>
  <td>ALL</td>
  <td>idx_table_in_question_tmst_id</td>
  <td></td>
  <td></td>
  <td></td>
  <td>968234</td>
  <td>10.0</td>
  <td>Using where; Using temporary; Using filesort</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Can anyone help explain how I make an index it will actually use by default?</p>
  <p>Thanks.</p>