CODEWITHSUNDEEP
mysqlsql
Kayla
Kayla

Reputation: 11

What is the most efficient way to select multiple rows by a set of 100 thousands IDs in sql

I know that you can use

SELECT * 
  FROM table 
 WHERE id IN (ids)

In my case, I have 100,000 of ids.

I'm wondering if MySQL has a limit for IN clause. If anyone knows a more efficient way to do this, that would be great!

Thanks!

Upvotes: 1

Views: 1133

Answers (2)

Valeriu Ciuca
Valeriu Ciuca

Reputation: 2094

Bill Karwin suggestions are good.

The number of values from IN clause is only limited by max_allowed_packet from my.ini

MariaDB creates a temporary table when the IN clause exceeds 1000 values.

Another problem with such number of IDs is the transfer of data from the PHP script (for example) to the MySQL server. It will be a very long query. You can also create a stored procedure with that select and just call it from you script. It will be more efficient in terms of passing data from your script to MySQL.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562368

Just this week I had to kill -9 a MySQL 5.7 Server where one of the developers had run a query like you describe, with a list of hundreds of thousands of id's in an IN( ) predicate. It caused the thread running the query to hang, and it wouldn't even respond to a KILL command. I had to shut down the MySQL Server instance forcibly.

(Fortunately it was just a test server.)

So I recommend don't do that. I would recommend one of the following options:

  1. Split your list of 100,000 ids into batches of at most 1,000, and run the query on each batch. Then use application code to merge the results.

  2. Create a temporary table with an integer primary key.

    CREATE TEMPORARY TABLE mylistofids (id INT PRIMARY KEY);

    INSERT the 100,000 ids into it. Then run a JOIN query for example:

    SELECT t.* FROM mytable AS t JOIN mylistofids USING (id)

Upvotes: 1

Related Questions