In the research group where I work, we must solve following problem hundreds, maybe thousands of times every day: given a set of putative gene names (typically a few hundred of them), flag those that are not in our (MySQL) database. This problem is solved in a number of ways by our various applications and scripts. I would like to optimize the process.
The simplest approach, of course, is to iterate over the list of gene names (after removing any duplicates, of course), and for each gene name perform something like
SELECT count(*) FROM gene WHERE genename = 'GENENAME-XYZ';
SELECT * FROM gene WHERE genename = 'GENENAME-XYZ';
and detect the condition where the search fails.
This means performing one such query for usually hundreds, often thousands, of genes.
(BTW, most of these queries are usually performed by scripts written in Python, R, Perl, MATLAB, etc. using those language's DBI facilities.)
Alternatively, one could concoct a (typically huge) SQL statement of the form
SELECT DISTINCT genename FROM gene \ WHERE genename IN ('GENENAME-001', 'GENENAME-002', ..., 'GENENAME-789');
...and from the result deduce the subset of invalid gene names.
Maybe it's just me, but I pick up some potent code smell from such huge SQL statements.
Bottom line, I don't have an entirely satisfactory solution, but since the problem must be very common, I figure that there must be standard solutions for it. If this is correct, please let me know what they are.
BTW, assume that access to the DB is read-only. In particular, please rule out any solution that involves creating temporary tables.
Also, in case it matters, ours is a MySQL database.