Use terms from text file in SQL WHERE ... IN clause
I'm running SQL query for MySQL server with
... where name in ("term1","term2","term3")
I would like to get the list of terms from a text file with one term per line. How can I do this?
What about if this list of terms will get large? 10K, for example. Will this be efficient or I should use another approach? May be temporary table and join? The database is read-only.
I am not so familiar with MySQL but I see something like this where you can load a text file in a table as you suggested in your question:-
LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, column2, column3);
and then use joins for getting data.
Generally, using a WHERE ... IN statement gets very slow after a couple of hundred/thousand terms.
Also, if the table is read-only, then you won't be able to add indexes to make it any faster, so, like astander said, I would suggest a temporary table with a join.
I think your best bet might be to read this into a temp table/ table used specifically for this look up.
This way you can index the table, and use an inner join/ where in sub query.