0

I have a task I have to do that involves checking almost 1 million values for matches in an MYSQL database. At the moment I just have a simple "in" statement with 50,000 values (this is the limit for the DB) at a time to check for matches but this is quite time-consuming. Any suggestions on how I could do this more efficiently?

Sample statement showing what I am doing:

SELECT column FROM table
WHERE column IN (value1, value2, value3,....) --All the way to value50000

I only have read access to the DB. I am unable to create temp tables.

Thanks in advance for any suggestions!

Dylan
  • 1
  • 1
  • what do the values look like? if numbers, how big or small? if strings, how long? – ysth Nov 15 '21 at 20:04
  • That's some BS requirements not having access to build a temp table. Can you pull the data out of MySQL and do this externally? – JNevill Nov 15 '21 at 20:07
  • yes, how many rows are there in the table? would it be faster to `select distinct column from table` and look for your matches client-side than loop doing 50000 at a time? – ysth Nov 15 '21 at 20:10
  • how slow is "quite time-consuming"? what does `select @@max_allowed_packet;` show? even if you can't get write access, can you have the database admin increase that to allow you to do a single query? – ysth Nov 15 '21 at 20:13
  • please edit your question to show output of `show create table yourtablename;` and `explain select column from table where column in (your 50000 values);` – ysth Nov 15 '21 at 20:18

0 Answers0