Search code examples
mysqlsqlwhere-in

SQL select ... in (select...) taking long time

I have a table of items that users have bought, within that table there is a category identifier. So, I want to show users other items from the same table, with the same categories they have already bought from.

The query I'm trying is taking over 22 seconds to run and the main items table is not even 3000 lines... Why so inefficient? Should I index, if so which columns?

Here's the query:

select * from items
    where category in (
        select category from items 
        where ((user_id = '63') AND (category <> '0')) 
        group by category
    ) 
order by dateadded desc limit 20
Solution

  • Here is a query. And sure add index on category,user_id,dateadded

    select i1.* 
from items i1
inner join 
(select distinct 
           category 
        from items 
        where ((user_id = '63') AND (category <> '0'))
) i2 on (i1.Category=i2.Category)


order by i1.dateadded desc limit 20