MySQL Forums
Forum List  »  Newbie

Re: ERROR 1116 (HY000): Too many tables; MySQL can only use 61 tables in a join
Posted by: Bill Karwin
Date: June 06, 2006 01:14PM

If you are joining more than 61 tables, it is very likely that your data model is flawed.

I would guess that you're implementing an Entity-Attribute-Value (EAV) model. This doesn't scale well, as you've discovered.

I know of no way to increase the limit of 61 tables in a join in MySQL. It's probably an architectural limit, and unable to be increased without extensive enhancement to the MySQL internals.

Even if you could increase the limit, your data model will eventually exceed the higher limit, whatever you increase it to.

It would be much easier to fix your data model.

The only alternative I could suggest is to do the join in pieces, and combine the resulting data in your application. If I'm right about your EAV data model, this should be possible to do; put a limit on the number of attributes in your query generator, and have it generate multiple queries instead of one big query. Fetch all the results, and then push them all into some kind of hash-list data structure, keyed by the entity key from your database.

Bill K.

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.