为什么不使用索引？

Question

各位互联网上的好心人大家好。

我绞尽脑汁试图弄清楚为什么优化器没有使用我的索引来进行 Amazon Aurora 查询。该查询是根据用户通过应用程序 UI 创建的报告动态创建的，因此我无法更改查询本身。

查询使用这些限定符

WHERE
  table_in_question.deleted = 0
ORDER BY
  table_in_question.date_modified DESC,
  table_in_question.id DESC

我有一个​​索引“my_index”，它索引这些特定字段（table_in_question字段已删除，date_modified，ID），但MySQL不使用它。

该查询运行大约需要 1200 毫秒。如果我添加 FORCE INDEX (my_index)，大约需要 120 毫秒。可以说快了大约 10 倍 - 但除非我使用强制索引，否则它不会使用它。

根据 EXPLAIN 返回了大约 100 万行，所以我不认为这是不使用索引的情况，因为返回的行数很少。

完整查询是

SELECT
  case when some_table.id IS NOT NULL then some_table.id else "" 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
;

显示此

id	select_type	表	分区	类型	possible_keys	key	key_len	ref	rowsfiltered	1	Extra
table_in_question	PRIMARY	EXPLAIN		ALL	idx_table_in_question_tmst_id				968234	10.0	使用 where;使用临时的；使用 filesort

任何人都可以帮助解释我如何创建默认情况下实际使用的索引吗？

谢谢。