0

can u help me understand to difference between EXISTS and IN using mysql ?.
in my application i am using IN in my queries, since i am using memcached to store the IN values,
so i find IN more faster the EXISTS, for example :

String my_chached_string = Memecached.get('somekey')
"SELECT * FROM Table t WHERE t.fromId IN (" + my_chached_string + ")"

my question, is there a way to use EXISTS the same way, with constant values ?

thank you

shay te
  • 1,028
  • 3
  • 12
  • 23
  • possible duplicate of [NOT IN vs NOT EXISTS](http://stackoverflow.com/questions/173041/not-in-vs-not-exists) – Strawberry Feb 21 '15 at 21:41

1 Answers1

2

Here are some common coding patterns:

WHERE col = 'string'
WHERE col IN ('string') -- same as above
WHERE col IN ('string1', 'string2')
WHERE EXISTS ( SELECT * FROM tbl WHERE ... )

The EXISTS one is really solving a different situation; I don't understand why you threw it in. Even if there were an equivalent EXISTS pattern, it would necessarily be less efficient.

This pattern in terribly inefficient:

WHERE col IN ( SELECT ... )

It can often be made more efficient by transforming into a JOIN.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • i know that EXIST evaluate single true or false but IN compare multiple value, so exists is a bit better, in my case i have to make two exists queries to run my query, this is way i chose IN with PREFETCHED values (and its running faster)! i was just wondering if i can use EXISTS with PREFETCHED values, thank you – shay te Feb 22 '15 at 19:15
  • EXISTS with prefetched values will not be faster than IN with prefetched values. EXISTS without prefetching will beat IN. If your timings found otherwise, then run them a second time -- caching could have confused the timings. – Rick James Feb 22 '15 at 19:55
  • thanks rick, i guess my EXISTS queries are not so simple, i changed lots of queries from EXISTS to IN with prefetched values and my application performance was very imporoved – shay te Feb 23 '15 at 20:03