Why MySQL’s TDDL Optimizer Picks the Wrong Index and How to Fix It

Background

During a multi‑tenant migration, the HSF consumer success rate occasionally dropped to 99.99% due to slow SQL queries causing service timeouts.

Problem Statement

The problematic query aims to fetch the latest five messages within seven days for a specific merchant, filtering by several categories and tier='S'. The logical SQL is complex and spans multiple partitioned tables (iii_sss_msg_29, iii_sss_msg_30, …).

(
  select
    `iop_xxx_msg`.`id`,
    `iii_sss_msg`.`message_id`,
    `iii_sss_msg`.`title`,
    `iii_sss_msg`.`content`,
    `iii_sss_msg`.`id_seller`,
    `iii_sss_msg`.`id_user`,
    `iii_sss_msg`.`gmt_create`,
    `iii_sss_msg`.`gmt_modified`,
    `iii_sss_msg`.`is_read`,
    `iii_sss_msg`.`category`,
    `iii_sss_msg`.`sub_category`,
    `iii_sss_msg`.`description`,
    `iii_sss_msg`.`need_side_notice`,
    `iii_sss_msg`.`link_url`,
    `iii_sss_msg`.`btn_name`,
    `iii_sss_msg`.`gmt_create_l`,
    `iii_sss_msg`.`mobile_content`,
    `iii_sss_msg`.`tier`,
    `iii_sss_msg`.`requirement_id`,
    `iii_sss_msg`.`fk_template_id`,
    `iii_sss_msg`.`business_part`,
    `iii_sss_msg`.`business_id`
  from
    `iii_sss_msg_29` `iii_sss_msg`
  where
    (`iii_sss_msg`.`gmt_create` >= '2023-07-24 00:00:00'
     and `iii_sss_msg`.`gmt_create` < '2023-07-31 15:46:45.684'
     and `iii_sss_msg`.`id_user` = 500173482096
     and `iii_sss_msg`.`tier` IN ('S','A')
     and `iii_sss_msg`.`sub_category` IN (1000305,1000306,1000501,1000502))
    or (`iii_sss_msg`.`category` IN (10003,10005)
        and `iii_sss_msg`.`gmt_create` >= '2023-07-24 00:00:00'
        and `iii_sss_msg`.`gmt_create` < '2023-07-31 15:46:45.684'
        and `iii_sss_msg`.`id_user` = ...
        and `iii_sss_msg`.`sub_category` IN (1000305,1000306,1000501,1000502))
  order by `iii_sss_msg`.`gmt_create` desc
  limit 0,5
) union all ...

TDDL Optimizer

The application uses TDDL, which parses and rewrites the MySQL statement in the Matrix layer. TDDL reordered the WHERE clause to match the index idx_user(id_user,category), hoping to use it for optimization.

Index Analysis

The table has a composite index idx_user(gmt_create,id_user,category,sub_category), which covers all WHERE columns. However, the execution plan shows MySQL using idx_uer_query(id_user,category) instead, scanning far fewer rows.

Why MySQL Chose the Smaller Index

MySQL’s optimizer evaluates several factors: query predicates, index selectivity (cardinality), index size, data‑page size, and covering ability. Although idx_user matches more predicates, its first column gmt_create has a cardinality of 99,933, while id_user in idx_uer_query has a higher cardinality of 286,528, making the latter appear more selective.

Force Index Test

Using FORCE INDEX (idx_user) forces MySQL to use the larger index, resulting in ~1,955,218 rows scanned and 995 ms execution time, compared with ~13,948 rows and 95 ms when using idx_uer_query.

Improving the Index

Because the partition key is gmt_create, scanning the whole index leaf nodes is inevitable. The author redesigns the index to idx_user(id_user,category,sub_category,gmt_create,is_read), which aligns better with the query pattern. After the change, the optimizer consistently picks this index, scanning only about ten thousand rows.

Partition‑Key Selection

The current partition key gmt_create leads to frequent cross‑table joins when querying recent messages. A better choice is id_user, which would keep a merchant’s data together and avoid scanning many partitions.

Summary

The article demonstrates how TDDL’s optimizer can unintentionally reorder a query, causing MySQL to select a sub‑optimal index; it explains MySQL’s index‑selection criteria, shows how to verify and force the correct index, and provides practical guidelines for designing effective composite indexes and choosing appropriate partition keys.