CODEWITHSUNDEEP
phpmysql
Dan Hastings
Dan Hastings

Reputation: 3280

MySQL efficient way to select rows from table 1 where join does not exist

I have tried a bunch of answers for this and none of them are working for me.

Find records where join doesn't exist

Select * from table1 that does not exist in table2 with conditional

I have 2 tables. 1 is an item table with about 50,000 items. The second is a rating table with over 100,000 rows. I want to get all items that do not have any votes.

I have tried the following and it seems to be valid but it seems to freeze, likely due to it having to check through so many records. Is there a more efficient way for me to achieve this.

SELECT title FROM items LEFT JOIN itemvotes ON items.id = itemvotes.itemid WHERE itemvotes.vote IS NULL

Im sure this query will return results eventually, but i need this to be a quick thing that can be checked from a php web page so it needs to load within seconds.

Just to confirm. Took over 3 minutes for the query above to return results. It was valid, but way too slow.

Upvotes: 1

Views: 153

Answers (2)

Angel Iliikov
Angel Iliikov

Reputation: 694

Why not use something like: 

 SELECT * FROM `table1` WHERE `id` NOT IN SELECT `table1_id` FROM `table2`

table1_id is of course the foreign key.

Upvotes: 1

Tea Curran
Tea Curran

Reputation: 2983

A sub-query will likely be faster:

SELECT title FROM items
WHERE id NOT IN (
    SELECT itemid
    FROM itemvotes 
)

When you do the outer join it is first joining then searching, so it is searching 50,000 * 100,000 rows. If you do a sub-query it is looking at a maximum of 50,000 + 100,000. Of course you also have to make sure you have proper indexes on both tables.

Upvotes: 1

Related Questions