Maximum size of MySQL query?

tbach2

Does anybody know the maximum length that a mysql query can be? I have an app that builds a long "...WHERE field IN('1','3','37',...etc) " and I'm wondering at what point it might fail.

csn

I've seen posts that state it's 256 chars. Others stated it's 64K.

You can reduce the size of this query as so:

IN(1,3,37, ...)

if you're sure they're int's.

Sxooter

According to the crashme script the max size is 1 Meg.

Of course, according to that script the max size of a postgresql query is 16 meg.

Crashme used to say that Postgresql crashed on a large query. That was before they checked to see if they overran their own buffer in crashme. Then they limited the buffer to 16 Megs.

Postgresql, of course, has a limit of "how big is your computer." i.e. if it's big enough and makes you swap out all your memory and you run out of swap, then it's too big.

The reason crashme says 16 Meg? Cause that's the size of the buffer they allocated. Why doesn't it say >16Meg? Or unlimied? I don't know. Have to ask the guys who programmed crashme.

But again, the listed limit for all flavors of MySQL, is 1 Meg.

johnnyv

Holy crap, 1 meg is one huge huge query.
The biggest i have ever done would be a little under 1k.

I would hate to have to program queries that big, and 16mb forget it!

tbach2

whew, my biggest one is around 27K. (a kludge to get around lack of a way to do an intersection of two queries) Nice to know I've still got a lot of room until I go back rework it with temp tables.

Thanks sxooter 🙂

Sxooter

But 1 Meg isn't that big if you're insert base64 encoded jpegs or mp3s or something. then it's small and restrictive, which is why it kinda bugs me that MySQL.com report that Postgresql has a 16 Meg limit. They've been told numerous times there's no limit, but the listing never changes.

Oh well, it does look like they have someone updating the crashme script, so maybe now it's time to mention it again.

csn

Are there variables for the max query length in MySQL and PostgreSQL? I searched their sites for terms like 'max query length', 'max query buffer', etc. but didn't find anything. I found some old posts from 2000 saying that the max query length for PG was 64K back then.

Sxooter

I'm willing to guess that if there is a hard limit var you can edit in MySQL you have to do it during the build process.

For Postgresql, the limit was removed in, I believe 7.1 or 7.2, so I can see there having been a limit back then. Of course, that was back when you could only store big things in large objects, which don't go through queries, so to speak, for insertion (they're treated kinda like a file with a file handle and all.)

Since the 8k limit was removed in 7.1, I'd guess that's where the limit in query length was eliminated as well.