SQL查询需求：连接articles与log表统计Top3热门文章（优化性能）

Hey there! Let's figure out how to fix your query and pick the most performant solution for finding the top 3 most popular articles.

First, let's spot the issue with your original query #

Your right join is pulling in all records from the log table—including rows where path is null or doesn't match any article slug. Plus, using LIKE with a concatenated string isn't just less precise, it can also skip using indexes (if you have them) which hurts performance.

Here are the two correct solutions, plus a breakdown of which is faster #

Solution 1: Inner Join with Exact Path Match

SELECT a.title, COUNT(l.id) AS views
FROM articles a
INNER JOIN log l ON l.path = CONCAT('/article/', a.slug)
GROUP BY a.title, a.slug
ORDER BY views DESC
LIMIT 3;

• Why this works: The INNER JOIN only keeps records where there's a valid match between an article's slug and the log's path. Using an exact equality check (=) instead of LIKE lets the database leverage indexes on log.path (if you've created one) to speed up the join dramatically.
• Bonus: Adding a.slug to the GROUP BY clause avoids potential edge cases where two different articles might have the same title but unique slugs.

Solution 2: Pre-filter Log Data with a Subquery

SELECT a.title, COUNT(l.id) AS views
FROM articles a
INNER JOIN (
    SELECT id, SUBSTRING(path, 10) AS slug
    FROM log
    WHERE path LIKE '/article/%'
) l ON a.slug = l.slug
GROUP BY a.title, a.slug
ORDER BY views DESC
LIMIT 3;

• Why this works: The subquery first filters out all log entries that don't follow the /article/[slug] format, reducing the number of rows we need to join with the articles table. We then extract the slug from the path and match it directly to the articles.slug field.

Which solution is more performant? #

In most cases, Solution 1 is the better choice:

• Exact equality checks (l.path = CONCAT(...)) are faster than substring operations, especially if log.path has an index. The database can quickly look up all matching paths without scanning every row.
• It's more concise and avoids the overhead of a subquery.

The only time Solution 2 might edge out Solution 1 is if your log table has an enormous number of rows where path isn't in the /article/[slug] format. In that case, pre-filtering those rows first reduces the data set before the join, saving some time. But even then, if you have an index on log.path, Solution 1 will still likely be faster because index lookups are more efficient than full table scans with a LIKE filter.

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