Hey there! Let's break down how to speed up that sluggish query and understand how indexes can make all the difference.

针对当前查询的具体优化技巧 #

Your query is slow mainly because of two key issues: using a function on the id_sub_temp field (which kills index usage) and potentially missing targeted indexes for your joins and filters. Here's what to do:

1. 消除字段上的函数操作 #

The substring(requests.id_sub_temp, 3) = request_detail_2.id condition forces MySQL to run the substring function on every row in requests—that means it can't use any index on id_sub_temp. Instead:

• Add a computed column to the requests table to store the numeric part of id_sub_temp permanently:

  ALTER TABLE opadithree.requests 
  ADD COLUMN id_sub_temp_num INT AS (SUBSTRING(id_sub_temp, 3)) STORED;
  

• Then create an index on this new column:

  CREATE INDEX idx_requests_id_sub_temp_num ON opadithree.requests(id_sub_temp_num);
  

• Now rewrite your join condition to use this new column:

  requests.id_sub_temp_num = request_detail_2.id
  

This lets MySQL use the index for the join instead of scanning every row.

2. 优化id_sub_temp LIKE '2_%'过滤条件 #

Since id_sub_temp follows the pattern 2_数字 or 3_数字, the LIKE '2_%' is a prefix match—this can use an index if you create one. Add an index on id_sub_temp:

CREATE INDEX idx_requests_id_sub_temp ON opadithree.requests(id_sub_temp);

Even better, create a 覆盖复合索引 that includes the columns you need for the query, so MySQL doesn't have to go back to the table data:

CREATE INDEX idx_requests_sub_temp_covering ON opadithree.requests(id_sub_temp, id, id_temp, id_sub_temp_num);

This index covers the filter (id_sub_temp LIKE '2_%'), the join (id_sub_temp_num), and the columns you're selecting (id, id_temp).

3. 给其他表的关联&过滤字段加索引 #

• For opadi.request_detail_2: You're joining on id and filtering on theme to join with categories. Create a composite index here:

  CREATE INDEX idx_req_detail_id_theme ON opadi.request_detail_2(id, theme);
  

  This lets MySQL quickly find rows by id and get the theme value without extra table lookups.
• For opadithree.categories: You're joining on cu_code and filtering on atc = false, plus selecting id. Create a composite index that covers all these:

  CREATE INDEX idx_categories_cu_atc_id ON opadithree.categories(cu_code, atc, id);
  

  This index handles the join, the filter, and the selected column in one go.

通用：如何通过索引提升MySQL查询速度 #

Indexes are like a table of contents for your database—they help MySQL find data without scanning every row. Here are core principles to remember:

• 避免对索引字段做函数/计算操作: 任何像SUBSTRING(col, n)、col + 5或LOWER(col)的操作都会让MySQL忽略索引。尽量预计算值（比如我们之前创建的id_sub_temp_num）或者调整查询使用原始字段。
• LIKE只使用前缀匹配: 只有LIKE 'prefix%'能用到索引，LIKE '%suffix'或LIKE '%middle%'因为不是锚定字符串开头，无法使用索引。
• 复合索引遵循最左前缀原则: 如果你创建了索引(a, b, c)，它会对过滤条件包含a、a+b或a+b+c的查询生效，但单独用b或b+c的查询无法使用该索引。要根据查询习惯排序复合索引的字段顺序。
• 使用覆盖索引: 当查询的SELECT、JOIN和WHERE子句用到的字段都包含在某个索引里时，MySQL可以直接从索引获取所有需要的数据（无需回表查询），这是提升速度的关键技巧之一。
• 不要过度索引: 索引会加快读操作，但会减慢写操作（INSERT、UPDATE、DELETE），因为MySQL需要同步更新索引。只为实际用到的查询创建索引。
• 定期维护索引: 定期执行ANALYZE TABLE your_table;更新MySQL的索引统计信息，让查询优化器做出更优决策。对于碎片化的表（尤其是MyISAM，InnoDB相对较少），可以用OPTIMIZE TABLE your_table;清理索引碎片（注意：这个操作会锁表，要在低峰期执行）。

After applying these changes, your query should run significantly faster—test it with EXPLAIN to see if MySQL is using the indexes you created (look for type: ref or range in the EXPLAIN output instead of ALL).

内容的提问来源于stack exchange，提问作者Michele Della Mea