Use terms from text file in SQL WHERE ... IN clause

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Details here.