Query performance improvement with hundreds of WHERE clauses using IN-CodePudding

I want to query a database for a list of people using their names and birth dates. The list of people to be queried is supplied by the user in Python and my code constructs the query string to submit to the database. I am using something similar to the following query:

SELECT * FROM table WHERE 
    (name = 'Alice' AND dob = '2000/01/01') OR
    (name = 'Bob' AND dob = '2000/01/02') OR
    (name =  'Charlie' AND dob = '2000/01/03') OR ...

Which works alright, but gets quite slow when the number of people are hundreds with the same amount of WHERE clauses separated by ORs. Is there any way to make this query more efficient? I read that using one WHERE clause without ORs using IN might be faster but I fail to recognize how to incorporate the birth dates:

SELECT * FROM table WHERE name IN('Alice', 'Bob', 'Charlie',...) AND dob IN()

and somehow need to match the date of births with their respective names. Any good solutions for improving query performance?

CodePudding user response：

Insert the items to a holding table, and then join to the table. You might define the table to also contain a unique for each event or session.

CodePudding user response：

You can also put both fields in one IN() like:

SELECT * FROM table WHERE 
 (name,dob) IN (
     ('Alice','2000/01/01')
    ,('Bob', '2000/01/02')
    ,('Charlie', '2000/01/03')
  );

and create a composite INDEX over both fields name and dob

CodePudding user response：

You can create a TEMPORARY TABLE and bulk-insert the searched tuples. Then you can either join the two tables, or use a sub-select query. You should have a composite index on the two fields in at least one of the tables.

A simpler approach, which might work well enough, is what Bernd suggested.

Either way, keep in mind you should probably use dynamic parameters, to avoid a potential SQL injection attack. If using a (temporary) table, use parameters for the INSERT query. Otherwise, for the IN clause.