Use terms from a text file in SQL WHERE ... IN section

Question

I am running a SQL query for a MySQL server using

... where name in ("term1","term2","term3") 

I would like to get a list of terms from a text file with one expression per line. How can i do this?

How about if this list of terms becomes large? 10K, for example. Will it be effective, or should I use a different approach? Could it be a temporary table and join? The database is read-only.

Answer 1

I am not very 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 the connections to get the data.

Details here .

Answer 2

Typically, using a WHERE ... IN statement is very slow after several hundred / thousand terms.

Also, if the table is read-only, then you won’t be able to add indexes to make it faster, so as a passerby would say, I would suggest a temporary table with a join.

Answer 3

I think your best bet might be to read this in the temp / table used specifically for this search.

This way you can index the table and use the inner join / where in the subquery .