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