Menu
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.
237
MyISAM permits data and index files to grow up to 256TB by default, but this limit can be changed up to the maximum permissible size of 65,536TB (2567 − 1 bytes).
In MySQL the LIMIT clause is used with the SELECT statement to restrict the number of rows in the result set. The Limit Clause accepts one or two arguments which are offset and count. The value of both the parameters can be zero or positive integers.
MyISAM tables have a default limit set to 256TB for data and index files, which you can change to 65,536TB maximum. InnoDB maximum size for tables is 256TB, which corresponds to the full tablespace size.
Can MySQL handle 100 million records? Sure, and a whole lot more. I've personally worked with single tables in MySQL that had ten billion records.
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
Having to ask about limits when either doing a SQL query or database design is a good indicator that you're doing it wrong.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
