查询卡在“Creating Sort Index”，换表后耗时激增求助

Hey there, let's dig into why your query's suddenly stuck on Creating Sort Index and taking way longer than expected after switching to a new table. That stage usually means MySQL is struggling to sort your result set—either because it lacks proper indexes, the data volume has grown, or the query itself has inefficiencies that the new schema amplified. Here are actionable steps to fix this:

1. Audit Indexes on Sorting & Filtering Fields #

The Creating Sort Index phase ties directly to MySQL needing to sort data without leveraging an existing index. Since you switched tables, odds are the new schema is missing critical indexes your old table relied on:

• For the subquery generating datetime_field: Add a composite index on (date_date, date_time)—this speeds up the string-to-datetime conversion and reduces the raw data the subquery returns.
• For the main query's grouping/sorting fields: If you're grouping by date(datetime_field), str_1, str_2, etc., consider adding a covering composite index that includes these fields plus the aggregated sum_field_1/sum_field_2. This lets MySQL pull all needed data directly from the index without hitting the table, eliminating the need for a temporary sort index.
• Check the timezone table (aliased as t): Ensure the field you're joining on (and t.timezone itself) has an index—unindexed joins force full table scans that compound sorting delays.

2. Optimize the Subquery to Reduce Result Set Size #

Your subquery is likely generating far more data than necessary, making the subsequent sort operation exponentially slower:

• Filter early: Add a WHERE clause to the subquery to limit data to only the date range you care about (e.g., WHERE date_date >= '2024-01-01'). Don’t let it process the entire table if you don’t need to.
• Replace string concatenation with proper datetime conversion: Instead of concat_ws(' ', date_date, date_time), use STR_TO_DATE(CONCAT(date_date, ' ', date_time), '%Y-%m-%d %H:%i:%s') to create a proper DATETIME type. This makes functions like date(), dayname(), and sorting much faster, and lets you index the datetime_field directly if you store it (or compute it in the subquery with an index on the source fields).

3. Check Data Volume & Pre-Aggregate If Needed #

If the new table has significantly more rows than the old one, sorting that larger dataset will naturally take longer:

• Run SELECT COUNT(*) FROM your_new_table and compare it to the old table’s row count. If it’s 10x+ larger, consider pre-aggregating data: Use a cron job or ETL process to compute daily/weekly summaries into a separate table, then query that summary table instead of running the full aggregation every time.

4. Minimize Overhead from convert_tz #

Calling convert_tz twice per row (inside date() and time()) adds unnecessary computation, especially when combined with sorting:

• Precompute timezone-converted fields: Add columns like local_date and local_time to your new table, and populate them via triggers or during data ingestion (instead of computing them on the fly in the query). This eliminates runtime conversion overhead entirely.
• If precomputing isn’t an option, ensure t.timezone values are consistent or limited—avoid joining to a large timezone table if you can hardcode common timezone strings (e.g., 'UTC') for specific use cases.

5. Analyze the Execution Plan #

Run EXPLAIN ANALYZE (MySQL 8.0+) or EXPLAIN with your query to pinpoint exactly where the bottleneck is:

• Look for type: ALL in the output—this means a full table scan is happening, which kills performance for large datasets.
• Check the Extra column for Using filesort—this confirms MySQL is using disk-based sorting (the "Creating Sort Index" stage). If you see this, your indexes aren’t covering the sorting/grouping needs.

Example Optimized Query Snippet #

Here’s a quick tweak to your subquery to use proper datetime conversion and early filtering:

SELECT 
  STR_TO_DATE(CONCAT(date_date, ' ', date_time), '%Y-%m-%d %H:%i:%s') AS datetime_field,
  str_1, str_2, str_3, str_4,
  CEILING(SUM(agg_field1)) AS sum_field_1,
  SUM(agg_field2) AS sum_field_2
FROM your_new_table
WHERE date_date BETWEEN '2024-01-01' AND '2024-01-31' -- Filter early!
GROUP BY datetime_field, str_1, str_2, str_3, str_4

内容的提问来源于stack exchange，提问作者josibake