Whats better- query with long 'where in' condition or many small queries?

Question

Maybe it's a little dumb, but i'm just not sure what is better. If i have to check more than 10k rows in db for existanse, what i'd do?

#1 - one query

select id from table1 where name in (smth1,smth2...{till 30k})

#2 - many queries

select id from table1 where name=smth1

Though, perfomance is not the goal, i don't want to go down with mysql either ;) Maybe, any other solutions will be more suitable... Thanks.

upd: The task is to fetch domains list, save new (that are not in db yet) and delete those that dissappeared from list. Hope, it'll help a little...

Answer 1

What you should do is create a temp table, insert all of the names, and (using one query) join against this table for your select.

select id 
from table1 t1
inner join temptable tt on t1.name = tt.name

Answer 2

The single query will most likely perform better as the second will give a lot of round-trip delays. But if you have a lot of names like in your example the first method might cause you to hit an internal limit.

In this case it might be better to store the list of names in a temporary table and join with it.

Answer 3

Depending on your future needs to do similar things, you might want to add a function in the database 'strlist_to_table'. Let the function take a text where your input is delimited by a delimiter character (possibly also passed to function), split it on the delimiter to create a on-the-fly table. Then you can use

where in strlist_to_table('smth1|smth2', '|')

and also get protection from sql injection (maybe little Bobby Tables appears in the input).

Just my 2 cents...

Answer 4

I'm not sure how flexible your application design is, but it might be worth looking into removing the delimited list altogether and simply making a permanent third table to represent the many-to-many relationship, then joining the tables on each query.