CODEWITHSUNDEEP
mysql
reefine
reefine

Reputation: 853

MySQL Query "IN" 500,000 Records

SELECT value FROM table WHERE id IN ("1","2","3", ... "500000")

Have a query that reads like the above with about 500,000 values in the parentheses.

Is there a better way of performing a search? This method is very cumbersome/slow.

Upvotes: 2

Views: 522

Answers (3)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

You could do the following:

Step 01 : Make a table of the IDs

DROP TABLE IF EXISTS lookup_ids;
CREATE TABLE lookup_ids SELECT id FROM `table` WHERE 1=2;
ALTER TABLE lookup_ids ADD PRIMARY KEY (id);

Step 02 : You INSERT all 500,000 values INTO lookup_ids

Step 03 : Make a decent covering index on table

ALTER TABLE `table` ADD UNIQUE INDEX id_value_ndx (id,value);

Step 04 : Perform the JOIN

SELECT t.value FROM `table` t INNER JOIN lookup_ids i USING (id);

or

SELECT t.value FROM `table` t NATURAL JOIN lookup_ids i;

CAVEAT : Steps 1-3 do not need to be repeated unless you have a different set of lookup ids to load

Upvotes: 2

Mariusz Sakowski
Mariusz Sakowski

Reputation: 3280

you are close to mysql packet size limit (it's 16MB) so it's certainly bad idea.

from which source do you have these half million ids? couldn't you store them in database and perform join?

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135809

Put the 500,000 values into another table and join against it.

SELECT t.value
    FROM table t
        INNER JOIN NewTempTable n
            ON t.id = n.id

Upvotes: 10

Related Questions