0

I'm a little new to joins, so I'm not even sure if this is possible. I've been Googling and trying a few things..

What I need:

Select data.id where the corresponding user2data.user_id does not exist where user2data.user_id = 'X'

Exciting right? :D

What works:

SELECT * FROM data WHERE NOT EXISTS (SELECT * FROM user2data WHERE user2data.user_id=1 AND user2data.data_id=data.id) LIMIT 100;

However, it's slow, even though all 3 columns are indexed. I tried an OUTER JOIN for this purpose from another SO answer, but it's EVEN SLOWER than the above. What I need is an INNER JOIN.

Please let me know if this is actually possible, or if there is an alternative that takes advantage of the indexes.

Thanks and best

iautomation
  • 996
  • 10
  • 18
  • 1
    I'd suggest a *composite* index on `user2data` over `(user_id, data_id)`. – eggyal Aug 31 '16 at 16:51
  • You can't do an inner join to data that doesn't exist. – shawnt00 Aug 31 '16 at 16:55
  • Make sure the columns that you're joining on are indexed, then the outer join should be fast. – Barmar Aug 31 '16 at 16:58
  • @eggyal Thanks for the suggestion. It did speed it up some, but not near to the performance that an inner join would bring, under simular circumstances – iautomation Aug 31 '16 at 17:03
  • See @Quassnoi's blog for a performance discussion when performing anti-join operations over [not-nullable](https://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/) and [nullable](https://explainextended.com/2010/05/27/left-join-is-null-vs-not-in-vs-not-exists-nullable-columns/) columns. – eggyal Aug 31 '16 at 17:09
  • can you provide us with an sql fiddle with some data? makes it much easier to try for ourselves. – Jester Aug 31 '16 at 17:37

1 Answers1

0

Could be you can use left join 

SELECT * 
FROM data WHERE 
LEFT JOIN user2data ON (  user2data.user_id=1 AND user2data.data_id=data.id )
where user2data.data_id is null
LIMIT 100;
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • He said he tried this: *I tried an OUTER JOIN for this purpose* – Barmar Aug 31 '16 at 16:57
  • @Barmar You know I agree that OP mentioned using an outer join. But I also wonder if OP came up with the correct join condition as used above. – shawnt00 Aug 31 '16 at 17:02
  • He said he got it from another SO answer. There are many SO questions about how to get all rows that don't match another table, e.g. http://stackoverflow.com/questions/21633115/return-row-only-if-value-doesnt-exist?lq=1 – Barmar Aug 31 '16 at 17:04