Problem with "IN" while executing query.

Question

Hi,

I am trying to search employees based on their code and department.

I triggered a query using IN operator which has 1200 employee codes. When i executed the query I got an exception ("Maximum 1000 records allowed in IN ").

Can anyone help me out with this issue.

Answer 1

Uploading those records to a temporary table and using a join is almost certainly going to be much faster. As it is, the parser has to build a huge condition tree for the IN.

Note: I don't know if MYSQL supports the transaction-level temporary tables of Oracle or SQLServer, but even a "real" table should be faster, particularly if created once and reused. There are, of course, many variables such as the ability to batch inserts to that table, cost of writing transactions logs, &c.

Rereading your question: where do you get those employee codes? Can you get them from a table or join?

Answer 2

Create a (temporary) table which will contain your employee codes, fill it, and query like this:

SELECT *
FROM mytable
WHERE empcode IN (SELECT mycode FROM temptable)

Answer 3

Many DBMS don't allow a query like

SELECT * FROM table WHERE col IN (...)

with more than 1,000 values in the list. Maybe it is possible to split it up using chunks of less than 1,000 values:

SELECT * FROM table WHERE col IN (...) OR col IN (...)

or

SELECT * FROM table WHERE col IN (...)
UNION
SELECT * FROM table WHERE col IN (...)

(Although it would not make sense and is unlikely to work).

Otherwise, you should store your values in a temporary table and use a JOIN instead. Where do you get your 1,200 codes from -- they don't happen to be in the same database? :)

Answer 4

Create an in-memory temporary table (small tables only), populate with stored procedure results. Perform a select with a join on the temporary table. Drop the temporary table.

CREATE TEMPORARY TABLE ENGINE=MEMORY tmp AS 
  SELECT code FROM employee; // in-memory temporary table (small tables)

SELECT col1, col2 FROM table 
    INNER JOIN tmp ON table.code = tmp.code; // join faster than subquery

DROP TEMPORARY TABLE tmp; // cleanup temporary table