June 16, 2006
Limit on Number of Joins in MySQL
I've been digging around to find documentation on the limit MySQL places on the number of tables in a query. The error is number 1116:
Error: 1116 SQLSTATE: HY000 (ER_TOO_MANY_TABLES) Message: Too many tables; MySQL can only use %d tables in a join
I can't seem to find documentation on what number triggers this error. There are a lot of references to a limit of 31 and 61 out on the web.
I wrote a quick script to generates a slew of tables and a large SQL statement to test the limit. MySQL 4.0.23 precompiled binaries (RPM "standard" package) running on RedHat ES Linux says the limit is 63 tables. MySQL 4.1.14 running on Gentoo Linux raises the error once statements get above 61 tables. MySQL 5.0.20 on Intel Mac is the same, once the statements gets above 61 tables the error is raised. I also found a RedHat ES 2 Linux box with MySQL 3.23.58 running. Limit there is 31 tables, so the folks who came up with this on my Google search were getting the error using old-school MySQL.
The data simplified and without narration:
- MySQL 3.23 - 31 table limit
- MySQL 4.0 - 61 table limit
- MySQL 4.1 - 61 table limit
- MySQL 5.0 - 61 table limit
The message from the MySQL client looks something like:
ERROR 1116 (HY000): Too many tables; MySQL can only use 61 tables in a join
And yes, many will be quick to point out that if you're bumping into this limit there's likely something really wrong with the data model. I know, I know. We've got complex software with a pretty controlled data model. However there are some wicked reporting tools that let the user put together all kinds of crazy combinations of data. In reporting it doesn't take much and the table count gets out of control so we limit the choices at the application level.
The question that doesn't seem to get answered on the forums or anywhere else is how the limit is determined and what governs it. I'm guessing it's a setting that's compiled into the binary? Anyone know?
Update: I just tried my tests on MySQL 4.0.23 (installed from RPM) on Red Hat 7.3, an older Linux distro. Limit came up at 31. So it's not based on the MySQL version, but something to do with the OS? Maybe it's determined by hardware?
Posted by mike at June 16, 2006 11:17 AM