mysql 61 table join limit
NickName:Zack Newsham Ask DateTime:2015-03-18T15:40:34

mysql 61 table join limit

Precursor: Maybe my db design just sucks.

I have a system, where a I record some set information about an element. Then other users can add details to these elements. I don't know ahead of time what the names of these details are or how many there will be. As such I store them in an element_detail table. In some cases I need to display these rows as columns, i.e. when exporting data the user can specify which details they want as part of the output. To do this I use a query of the following type:

select ed1.item_value as 'detail_1_name',ed2.item_value as 'detail_2_name' from element e 
left join element_detail ed1 on ed1.element_id=e.element_id and ed1.item_key='detail_1_name'
left join element_detail ed2 on ed2.element_id=e.element_id and ed2.item_key='detail_2_name' 

This worked nicely until I started looking at 61+ details in a single dataset. At which point I got: CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join

How can I change my design so I don't get this, preferably without having to do 61 separate queries and build the dataset myself. I'm using PHP/Yii as the development environment.

Copyright Notice:Content Author:「Zack Newsham」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/29116500/mysql-61-table-join-limit

More about “mysql 61 table join limit” related questions

Raising mySQL 61 table join limit

I have a Drupal 6 application that requires more joins than that 61 table join mySQL limit allows. I understand that this is an excessive number, but it is ran only once a day, and the results are ...

Show Detail

mysql 61 table join limit

Precursor: Maybe my db design just sucks. I have a system, where a I record some set information about an element. Then other users can add details to these elements. I don't know ahead of time wh...

Show Detail

Work around the 61 table JOIN limit in MySQL by nesting subqueries within each other

I figured out that you can get around the 61 table join limit in MySQL by using subqueries. https://stackoverflow.com/a/20134402/2843690 I'm trying to figure out how to easily use this in a progra...

Show Detail

Work around the 61 table JOIN limit in MySQL by nesting subqueries within each other

I figured out that you can get around the 61 table join limit in MySQL by using subqueries. https://stackoverflow.com/a/20134402/2843690 I'm trying to figure out how to easily use this in a progra...

Show Detail

Does join limit in MySQL includes UNIONs?

Here is a loin limits in MySql. Does it applies fo joins with unions? For example: SELECT * FROM TABLE1 JOIN ... //say 61 join here UNION ALL SELECT * FROM TABLE62 JOIN ... //and another 61 joins ...

Show Detail

Doctrine Inheritance and MySQL Join Table Limit

I have a problem with 61 join table limit of mysql. I have 57+ different classes extending Base Class which contain association to comments, likes, tags. And MySQL is crashing when i get most comme...

Show Detail

How to do subqueries to avoid the 61 join table limit problem using springboot, hibernate and mysql

To summarize my problem, I have an entity A and B as follows: When I try to find all entities A, following error occurs: creates a join with more than 61 tables. That's because mysql does not allo...

Show Detail

how many max join table at mysql? and how to count it?

Maybe this question is related with this one. I have Question.. My friend said that mysql has limitation on max join table. And I get this : The maximum number of tables that can be referenced ...

Show Detail

Is a MySQL JOIN with LIMIT optimized internally?

A table community as id: int, name: varchar. Another table category_people as community_id: int, person_id: int. A JOIN with LIMIT query could be SELECT b.user_id, group_concat(a.name SEPARATOR ',...

Show Detail

MySQL LEFT JOIN with DISTINCT or LIMIT. Need to LEFT JOIN only one value

MySQL table 18_8_ChartOfAccounts AccountNumber | VatReturnRowNumberForDebitTurnover ------------------------------------------- 1 | 61 2 | 55 MySQL table 2_1_journal

Show Detail