MySQL中为别名列添加索引：按total_reports排序查询优化

Hey there! Let's figure out how to fix that full table scan and speed up your query. First, let's break down what's causing the slowdown and then walk through the fixes step by step.

1. Clean up unnecessary conditions first #

Your name LIKE '%%' clause does absolutely nothing—it matches every possible name value. Removing this will eliminate unnecessary filtering overhead right off the bat, since the database won't waste time processing a condition that doesn't narrow down results at all.

2. Verify the id = '' condition is correct #

If id is a numeric column or a non-nullable field, id = '' might not behave like you expect. For numeric IDs, this would cast values to empty strings, leading to unexpected matches or even invalidating potential indexes. If you're trying to target rows where id is null, use id IS NULL instead. Double-check this against your business logic—this could be a hidden source of inefficiency if it's filtering more rows than intended.

3. Create a targeted composite covering index #

The core issue here is that your query is scanning the entire table because it lacks an index to support its filtering, grouping, and aggregation needs. A composite covering index will let the database pull all required data directly from the index without touching the main table, eliminating the full scan.

Here's the index you should create:

CREATE INDEX idx_world_status_time_name_agg ON world (status, time, name, last_update, world_name);

Let's break down why this works:

• status comes first because it's an equality filter (status = 1)—equality conditions should always lead composite indexes for maximum efficiency.
• Next is time (your range condition: time >= '2017-07-16'). Range conditions should follow equality filters in index order.
• name is included next since you're grouping by it—having name ordered in the index lets the database group rows without extra sorting overhead.
• Finally, last_update and world_name are added so the database can compute Max(last_update) and Count(world_name) directly from the index (this is called a covering index, and you'll see Using index in the EXPLAIN output if it's working correctly).

4. Optimized Query (with cleanup) #

Here's your query with the useless LIKE clause removed:

SELECT 
  Count(world_name) AS total_reports, 
  name, 
  Max(last_update) AS report 
FROM `world` 
WHERE 
  `id` = '' -- Confirm this is correct! Use `id IS NULL` if matching null values
  AND `status` = 1 
  AND `time` >= '2017-07-16' 
GROUP BY `name` 
HAVING `total_reports` >= 2 
ORDER BY `total_reports` DESC 
LIMIT 50 OFFSET 0;

5. Validate the fix with EXPLAIN #

Run EXPLAIN before and after adding the index to confirm the improvement. You should see:

• The type column showing range or ref instead of ALL (which indicates no more full table scan).
• The Extra column showing Using index (confirming the covering index is being used) and reduced or eliminated Using temporary; Using filesort entries (depending on your MySQL version and data distribution).

That should drastically cut down on the full table scan and speed up your query execution!

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