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. 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.

| improve this answer | |
  • That website is fantastic, and thanks for your answer :) – mu 無 Aug 22 '15 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 :) – mu 無 Aug 22 '15 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 Aug 22 '15 at 16:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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