Alternative, faster search for SQL query with internal query

[wsimmonds]

The idea of this query is to use one table as an index to another table, so that it can be used to perform customised searches without having to mess with the actual table that records are kept in. This query does the job, but is very slow. Ideally it will be searching for multiple records by multiple criteria (flag_id) eventually, so I need to speed it up.

I think it's the subqueries that are slowing it down. Any ideas how to construct it without subqueries or any other ways of making it faster?

SELECT *
FROM records
WHERE id IN (SELECT rec_id FROM flag_values WHERE (flag_id =7 AND flag_value_text='danny')) AND id IN (SELECT rec_id FROM flag_values WHERE (flag_id =8 AND flag_value_text='cook'))
UNION SELECT *
FROM records
WHERE id IN (SELECT rec_id FROM flag_values WHERE (flag_id =7 AND flag_value_text='lisa')) AND id IN (SELECT rec_id FROM flag_values WHERE (flag_id =8 AND flag_value_text='cook'));

 

[BNPMike]

Often sub-selects can be mapped to joins, and, just looking superficially, that looks to be the case for your query. Joins are usually faster than sub-selects.

Just join id to rec_id and then again to rec_id. I don't think you need to do a UNION - it looks like you can combine some of the WHERE terms.

You can do all this in QBE to speed things up and get the syntax correct. Just bring in two copies of flag_values.