Too many tables; MySQL can only use 61 tables in a join
Posted by: Devrishi Shandilya
Date: February 14, 2017 12:53AM

Hi,

I got following error with MySQL, I have a table named attribute_value and each user has 100+ attributes, to get data of a user, I have to do self join the table 100 times. Do we have any solution in MySQL for the same ....

Below mentioned query is for 4 attributes, what if we have 100 attributes ?

Table Schema :

CREATE TABLE `attribute_value` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`value` varchar(255) DEFAULT NULL,
`user_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FKb2f2gwh8prxl9nhd40hobb30r` (`user_id`),
CONSTRAINT `FKb2f2gwh8prxl9nhd40hobb30r` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=82501 DEFAULT CHARSET=utf8

-------------------------------------------------------------------------------
Query pattern :

SELECT distinct av.user_id ,contact_lastName.value AS contact_lastName,
contact_mailingAddress.value AS contact_mailingAddress,contact_locale.value AS contact_locale,contact_title.value AS contact_title,group_name
FROM attribute_value AS av
LEFT JOIN attribute_value AS contact_lastName ON av.user_id = contact_lastName.user_id AND contact_lastName.name='contact_lastName'
LEFT JOIN attribute_value AS contact_mailingAddress ON av.user_id = contact_mailingAddress.user_id AND contact_mailingAddress.name='contact_mailingAddress'
LEFT JOIN attribute_value AS contact_locale ON av.user_id = contact_locale.user_id AND contact_locale.name='contact_locale'
LEFT JOIN attribute_value AS contact_title ON av.user_id = contact_title.user_id AND contact_title.name='contact_title'
LEFT JOIN user_group AS ug ON av.user_id = ug.user_id
LEFT JOIN group_entity AS ge ON ge.id = ug.group_id
-----------------------------------------------------------------------

Options: ReplyQuote


Subject
Written By
Posted
Too many tables; MySQL can only use 61 tables in a join
February 14, 2017 12:53AM


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.