CODEWITHSUNDEEP
mysqlin-operator
PSR
PSR

Reputation: 40318

Alternative to in operator in mysql

I see In operator alternative in mysql

I have nearly 25,000 ids.I am using in operator on that.Then i am getting Stackoverflow Exception.Is there any other alternative for IN operator in mysql.

Thanks in advance..

Upvotes: 1

Views: 6298

Answers (2)

Tom Mac
Tom Mac

Reputation: 9853

You could do the following:

1 - Create a MySQL Temporary Table

CREATE TEMPORARY TABLE tempIdTable (id int unsigned not null primary key);

2 - Insert All Your ids into the Temporary Table

For every id in your list:

insert ignore into myId (id) values (anId);

(this will have the added bonus of de-duplicating your list of ids ready for the final step)

3 - Join Against the Temporary Table

SELECT t1.* FROM myTable1 t1 INNER JOIN tempIdTable tt ON t1.id = tt.id;

The temporary table will disappear as soon as your connection is dropped so your don't have to worry about dropping it before you create it next time.

Upvotes: 1

Idan Arye
Idan Arye

Reputation: 12603

If the ID's are in another table:

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);

then you can use a join instead:

SELECT table1.* FROM table1 INNER JOIN table2 ON table1.id = table2.id;

Upvotes: 3

Related Questions