MySQL子查询优化 - 不在（子查询）中

Question

I'm trying to optimize the following query. I'm thinking an outer join would do the trick, but I can't wrap my mind around how to put it together.

// ---------------------------------
// Simplified representation of data
// ---------------------------------
create table views (
   user_id,
   article_id
)

create table article_attributes (
   article_id,
   article_attribute_id
)

create table articles (
   id,
   title,
   date
)

Views table has tens of millions of records. Articles table has a couple hundred thousand.

I'm trying to match all articles with a certain attribute associated with it, and that have not been viewed by a user.

What I have tried, but doesn't scale well:

select a.title, a.sid as article_id, a.total_views as times_read, a.date 
from articles a 
join article_attributes att on att.article_id = a.sid 

where a.sid not in( 
   select v.article_id 
   from views v
   join article_attributes att on att.article_id = v.article_id 
   where user_id = 132385 
   and att.article_attribute_id = 10
   group by v.article_id 
) 
and att.article_attribute_id = 10 
and a.date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 day) 
order by total_views desc 
limit 5

This works fine, but gets significantly slower the more articles the user has viewed. Any ideas or suggestions would be appreciated.

Answer 1

SELECT a.title, a.sid AS article_id, a.total_views AS times_read, a.date
FROM articles a 
    JOIN article_attributes att 
        ON a.id = att.article_id AND att.article_attribute_id = 10 
    LEFT JOIN views v 
        ON a.id = v.article_id AND v.user_id = 132385  
WHERE v.user_id IS NULL

1. The first join gets you only the articles with the given attribute.
2. The second join takes the first join's result and returns rows with the user_id and all the remaining rows from first result that don't have the user_id.(Basically ALL articles with attribute 132385 with the user_id being either 10 or NULL)
3. Then all we want is that result where user_id is NULL

Try to avoid nested queries and let the engine do it's job. Note you can tag on your other filters (DATE, ORDER BY) on the end.