Why isn't it using the index?

Question

Hello kind people of the internet.

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.

The query uses these qualifiers

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

I have an index, "my_index", which indexes these specific fields (table_in_question fields deleted, date_modified, ID) but MySQL doesn't use it.

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.

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.

The full query is

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
;

EXPLAIN shows this

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	table_in_question		ALL	idx_table_in_question_tmst_id				968234	10.0	Using where; Using temporary; Using filesort

Can anyone help explain how I make an index it will actually use by default?

Thanks.