From the mysql docs,

1 - on joins,

The maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view.

2 - row size

Every table (regardless of storage engine) has a maximum row size of 65,535 bytes

3 - on number of columns

There is a hard limit of 4096 columns per table

InnoDB permits up to 1000 columns.]

Is there an explanation of how these numbers have been calculated, or are these all constants assumed during system design?

1 Answer 1

  1. The 61 is probably arbitrary. If you have a 61-way JOIN, you have an awful schema.

  2. The row size limit is not quite right. What page is that on? It is ignoring that MEDIUM/LONGTEXT/BLOB fields are not included in that limit. (But, for that matter, I don't know where 64K-1 applies.) In InnoDB, there is an "about 8K" limit excluding TEXT, etc that can be pushed to off-block storage.

  3. The InnoDB limit was raised to 1017 recently. The 4096 limit smells like a 12-bit fields in the Handler (Engine-agnostic) code.

I like limits, too. Here are some more.

  • That website is fantastic, and thanks for your answer :) Commented Aug 22, 2015 at 8:00
  • I am going through other posts on your site, and they are really informative. I've been looking for a similar resource for a few days now to get a better understanding of mysql internals. I will offer a bounty for your answer once the link is available to me. Thanks again :) Commented Aug 22, 2015 at 8:11
  • 1
    Thanks. My posts were derived from interacting with hundreds of DBAs, engineers, architects, plus from thousands of forum questions. I see a recurring question/problem, I think about it, study it, experiment, then I post a focused solution. The charset blog needs a major overhaul; I'm not happy with it. I started worrying about that topic a decade ago. I'll bet your handle gets Mojibaked into mu ç„¡.
    – Rick James
    Commented Aug 22, 2015 at 16:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.