如何优化关联表ORDER BY语句性能？需保留排序与左连接空行

Hey there, let's break down how to speed up that query while keeping all your required logic intact. The 0.5-second delay is almost certainly coming from a filesort operation triggered by the ORDER BY s.test DESC clause—when you LEFT JOIN two tables and then sort the full result set, MySQL often has to shuffle data around on disk, which gets slow as your tables grow. Here's how to fix it:

1. Add a Targeted Index to something #

First, let's give MySQL a way to avoid that costly filesort by building an index that directly supports your sort and join:

CREATE INDEX idx_something_test_customer ON something (test DESC, customer_id);

This index does two key things:

• It sorts something rows by test in descending order exactly as your query needs, so MySQL doesn't have to re-sort later.
• It includes customer_id right in the index, meaning MySQL can pull all the data it needs for the JOIN without having to jump back to the main table (this is called a "covering index").

Your customers table already has a primary key on customer_id, which is perfect for fast lookups when joining—no changes needed there.

2. Rewrite the Query (For Even Better Performance) #

If adding the index still isn't giving you enough speed, try rewriting the query to limit the data we process before sorting. The idea is to grab only the top 25 relevant rows from something first, then join to customers:

SELECT c.name
FROM customers c
LEFT JOIN (
    -- Grab the top 25 rows from something sorted by test DESC
    SELECT customer_id, test 
    FROM something 
    ORDER BY test DESC 
    LIMIT 25
) s USING(customer_id)
-- Keep the same sort logic: non-null test values first, then nulls
ORDER BY COALESCE(s.test, -999999) DESC 
LIMIT 25;

The COALESCE here just ensures null test values (from customers with no matching something row) stay at the end—matching the behavior of your original query (MySQL defaults to putting nulls last in ORDER BY DESC).

This rewrite cuts down the amount of data we need to sort drastically: instead of sorting every joined row, we only sort the tiny subset from the subquery plus any unmatched customers.

3. Verify with EXPLAIN #

Always check the execution plan to make sure our changes are working. Run:

EXPLAIN SELECT c.name FROM customers c LEFT JOIN something s USING(customer_id) ORDER BY s.test DESC LIMIT 25;

Look for:

• No Using filesort in the Extra column (that means we're using the index for sorting)
• idx_something_test_customer listed in the key column for the something table

Quick Side Note #

I noticed your customers table has an index named namne (probably a typo for name)—this index doesn't help with your current query, so you can ignore it or fix the name if that was an accident. Also, since you're using MyISAM, remember to run OPTIMIZE TABLE customers; and OPTIMIZE TABLE something; occasionally to clean up table fragmentation.

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