拆分子查询实现多计数？现有统计SQL查询优化建议咨询

Great question! Your current query works as intended, but we can tweak it to be more efficient—let’s dive into how and why.

What’s Less Ideal About the Original Query #

Your current approach uses three separate subqueries:

• Two against the likes table (one for likes, one for dislikes)
• One against media_thumbnail for views

This means the database has to scan the likes table twice for the same media_id, and you’re passing the same parameter three times. While this might not feel like a big deal for small datasets, it adds unnecessary overhead as your tables grow or if this query runs frequently.

Optimized Query: Single Scan + Join #

We can use conditional aggregation to calculate both likes and dislikes in a single pass over the likes table, then join with media_thumbnail to get views. Here’s a cleaner, more efficient version:

SELECT
  COUNT(CASE WHEN like_state = true THEN 1 END) AS likes,
  COUNT(CASE WHEN like_state = false THEN 1 END) AS dislikes,
  mt.media_views AS views
FROM likes l
RIGHT JOIN media_thumbnail mt 
  ON l.media_id = mt.media_id
WHERE mt.media_id = ?
GROUP BY mt.media_id, mt.media_views;

Why This Works:

• The RIGHT JOIN ensures we still get view data even if there are no likes/dislikes for the media (likes/dislikes will return 0 in that case)
• We only scan the likes table once, not twice
• You only need to pass the media_id parameter once

If your media_thumbnail table has a unique constraint on media_id (which it should, since each media entry should have one thumbnail record), you can also use a LEFT JOIN with a pre-aggregated subquery for even tighter performance:

SELECT
  COALESCE(l.likes, 0) AS likes,
  COALESCE(l.dislikes, 0) AS dislikes,
  mt.media_views AS views
FROM media_thumbnail mt
LEFT JOIN (
  SELECT
    media_id,
    COUNT(CASE WHEN like_state = true THEN 1 END) AS likes,
    COUNT(CASE WHEN like_state = false THEN 1 END) AS dislikes
  FROM likes
  WHERE media_id = ?
  GROUP BY media_id
) l ON mt.media_id = l.media_id
WHERE mt.media_id = ?;

This version first filters and aggregates only the relevant media_id from likes, then joins to get the view data—perfect if your likes table is large.

Pro Tip: Indexes for Speed #

To make this query fly, add these indexes:

• On likes: Create a composite index (media_id, like_state). This lets the database quickly find all entries for your target media_id and access the like_state value without needing to scan the entire table (a covering index).
• On media_thumbnail: Ensure media_id is either the primary key or has a unique index. This makes the WHERE mt.media_id = ? lookup instantaneous.

Bonus: Simplified Syntax (Database-Specific) #

If you’re using MySQL or MariaDB, you can use a shorter syntax for the aggregation since boolean values are treated as 1/0 numerically:

SUM(like_state = true) AS likes,
SUM(like_state = false) AS dislikes

This does the same thing as the COUNT(CASE...) approach but is more concise. Stick with the CASE version if you need standard SQL compatibility across databases.

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