优化三表关联查询速度：ORDER BY导致查询超时求助

Hey there, let's break down why that ORDER BY is tanking your query and fix it up—this is a super common pain point with large datasets, so we’ve got straightforward, effective solutions here.

First, let’s diagnose the root issue: when you add ORDER BY score_sec DESC, your database is forced to join all three tables first (resulting in potentially hundreds of thousands of rows), then sort every single one of those rows just to pick the top 6. That sort operation is the bottleneck—it’s way too expensive for 300k+ records.

Here are the actionable optimizations you can implement right away:

• Add a targeted composite index to the scores table
  The biggest win here is letting the database quickly grab the top 6 highest score_sec values without sorting the entire table. Create a composite index that includes both the join field (id) and the sort field (score_sec):

  CREATE INDEX idx_scores_id_score ON scores(id, score_sec DESC);
  

  This index lets the database retrieve rows in the exact order you need, eliminating the need for a costly full-table sort.

• Filter first, join later (shrink your dataset early)
  Instead of joining all three tables upfront, pull the top 6 rows from the scores table first, then only join the other tables to those 6 records. This cuts down the data your database has to process drastically:

  SELECT so.id, so.title, so.cat, sr.img, se.score_sec
  FROM (
      -- Grab the top 6 highest score_sec records first
      SELECT id, score_sec
      FROM scores
      ORDER BY score_sec DESC
      LIMIT 6
  ) AS se
  -- Now join only with matching rows from the other tables
  JOIN details AS sr ON se.id = sr.id
  JOIN extended_details AS so ON sr.id = so.id
  ORDER BY se.score_sec DESC;
  

  Since we’re only working with 6 rows from the start, the joins and final sort become trivial operations.

• Verify indexes on join fields
  Make sure the id fields in extended_details and details are either primary keys (which automatically have indexes) or have dedicated indexes. If they don’t, add them to speed up join operations:

  -- If extended_details.id isn't indexed
  CREATE INDEX idx_extended_details_id ON extended_details(id);
  -- If details.id isn't indexed
  CREATE INDEX idx_details_id ON details(id);
  

  Without these indexes, the database has to scan entire tables to find matching id values, adding unnecessary slowdown even after fixing the ORDER BY issue.

• Keep your SELECT lean
  You’re already doing this, but a quick reminder: only select the fields you actually need (like so.id, so.title, etc.) instead of using SELECT *. This reduces the amount of data the database has to load and process.

The core idea here is to minimize the data your database handles at every step—by grabbing the smallest possible dataset first (the top 6 scores), you avoid the massive sort operation that was timing out your query.

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