How to check the existance of multiple IDs with a single query

Question

Hey there

I'm trying to find a way to check if some IDs are already in the DB, if an ID is already in the DB I'd naturally try to avoid processing the row it represents

Right now I'm doing a single query to check for the ID, but I think this is too expensive in time because if I'm checking 20 id's the script is taking up to 30 seconds

I know i can do a simple WHERE id=1 OR id=2 OR id=3 , but I'd like to know of a certain group of IDs which ones are already in the database and which ones are not

I don't know much about transactions but maybe this could be useful or something

any thoughts are highly appreciated!

Answer 1

Depends how you determine the "Group of IDs"

If you can do it with a query, you can likely use a join or exists clause.

for example

SELECT firstname
  from people p 
  where not exists (select 1 from otherpeople op where op.firstname = p.firstname)

This will select all the people who are not in the otherpeople table

If you just have a list of IDs, then use WHERE NOT IN (1,3,4...)

Answer 2

30 seconds for 20 queries on a single value is a long time. Did you create an index on the ID field to speed things up?

Also if you create a unique key on the ID field you can just insert all ID's. The database will throw errors and not insert those those ID's that already exist, but you can ignore those errors.