What is the size limitation for IN and NOT IN in MySQL

Question

I get out of memory exception in my application, when the condition for IN or NOT IN is very large. I would like to know what is the limitation for that.

Answer 1

Perhaps you would be better off with another way to accomplish your query?

I suggest you load your match values into a single-column table, and then inner-join the column being queried to the single column in the new table.

Rather than

SELECT a, b, c FROM t1 WHERE d in (d1, d2, d3, d4, ...)

build a temp table with 1 column, call it "dval"

dval  
----  
 d1  
 d2  
 d3

SELECT a, b, c FROM t1  
INNER JOIN temptbl ON t1.d = temptbl.dval

Answer 2

Having to ask about limits when either doing a SQL query or database design is a good indicator that you're doing it wrong.

Answer 3

I have used IN with quite large lists of IDs - I suspect that the memory problem is not in the query itself. How are you retrieving the results?

This query, for example is from a live site:

SELECT DISTINCT c.id, c.name FROM categories c 
LEFT JOIN product_categories pc ON c.id = pc.category_id 
LEFT JOIN products p ON p.id = pc.product_id 
WHERE p.location_id IN (
955,891,901,877,736,918,900,836,846,914,771,773,833,
893,782,742,860,849,850,812,945,775,784,746,1036,863,
750,763,871,817,749,838,986,794,867,758,923,804,733,
949,808,837,741,747,954,939,865,857,787,820,783,760,
911,745,928,818,887,847,978,852
) ORDER BY c.name ASC

My first pass at the code is terribly naive and there are about 10 of these queries on a single page and the database doesn't blink.

You could, of course, be running a list of 100k values which would be a different story altogether.

Answer 4

I don't know what the limit is, but I've run into this problem before as well. I had to rewrite my query something like this:

select * from foo
  where id in (select distinct foo_id from bar where ...)

Answer 5

@le dorfier, Thank You. We are also planning the same solution for the problem.

However, adding a new table in the existing installations, will need some extra validations and time - Hence as a temporary workaround we thought of splitting the query.

Answer 6

I only ever use IN and NOT IN when the condition is very small (under 100 rows or so). It performs well in those scenarios. I use an OUTER JOIN when the condition is large as the query doesn't have to look up the "IN" condition for every tuple. You just have to check the table that you want all rows to come from.

For "IN" the join condition IS NOT NULL

For "NOT IN" the join condition IS NULL

e.g.

/* Get purchase orders that have never been rejected */
SELECT po.*
FROM PurchaseOrder po LEFT OUTER JOIN 
     (/* Get po's that have been rejected */
     SELECT po.PurchaesOrderID
     FROM PurchaseOrder po INNER JOIN 
         PurchaseOrderStatus pos ON po.PurchaseOrderID = pos.PurchaseOrderID
     WHERE pos.Status = 'REJECTED'
     ) por ON po.PurchaseOrderID = por.PurchaseOrderID
WHERE por.PurchaseOrderID IS NULL    /* We want NOT IN */

Answer 7

Hi, I"m having a similar issue but only passing 100 3 digit ids in my IN clause. When I look at the stack trace, it actually cuts off the comma separate values in the IN clause. I don't get an error, I just don't get all the results to return. Has anyone had an issue like this before? If its relevant, I'm using the symfony framework... I'm checking to see if its a propel issue but just wanted to see if it could be sql