MySQL can only use 61 tables in a join; How can I bypass this error for the following query?
NickName:Piyush Das Ask DateTime:2016-06-10T18:05:29

MySQL can only use 61 tables in a join; How can I bypass this error for the following query?

I get a MySQL Error saying, I cannot use more than 61 tables in a join. I need to avoid this error. How do I do it? Please Help.

select
    view_pdg_institutes.user_id as User_ID,
    view_pdg_institutes.institute_id as Teacher_ID,
    view_pdg_institutes.institute_name as Institute_Name,
    view_pdg_institutes.user_email as Email,
    view_pdg_institutes.contact_person_name as Contact_Person,
    view_pdg_institutes.alternative_contact_no as Alternative_Mobile_No,
    view_pdg_institutes.primary_contact_no as Mobile_No,
    view_pdg_institutes.correspondance_address as Address,
    view_pdg_institutes.other_communication_mode as Preferred_Contact_Mode,
    view_pdg_institutes.size_of_faculty as Size_of_Faculty,
    view_pdg_institutes.operation_hours_from as Operation_Hours_From,
    view_pdg_institutes.operation_hours_to as Operation_Hours_To,
    view_pdg_institutes.teaching_xp as Teaching_Experience,
    view_pdg_institutes.installment_allowed as Installment_Allowed,
    view_pdg_institutes.about_fees_structure as About_Fees_Structure,
    view_pdg_institutes.no_of_demo_class as No_of_Demo_Classes,
    view_pdg_institutes.demo_allowed as Demo_Allowed,
    view_pdg_institutes.price_per_demo_class as Price_Per_Demo_Class,
    view_pdg_tuition_batch.tuition_batch_id as Batch_ID,
    view_pdg_batch_subject.subject_name as Subject_Name,
    view_pdg_batch_subject.subject_type as Subject_Type,
    view_pdg_batch_subject.academic_board as Academic_Board,
    view_pdg_batch_fees.fees_type as Fees_Type,
    view_pdg_batch_fees.fees_amount as Fees_Amount,
    view_pdg_tuition_batch.course_days as Course_Days,
    view_pdg_tuition_batch.days_per_week as Days_Per_Week,
    view_pdg_tuition_batch.class_duration as Class_Duration,
    view_pdg_tuition_batch.class_type as Class_Type,
    view_pdg_tuition_batch.course_length as Course_Length,
    view_pdg_tuition_batch.course_length_type as Course_Length_Type,
    view_pdg_tuition_batch.no_of_locations as No_of_Locations,
    view_pdg_tuition_batch.class_capacity_id as Class_Capacity_ID,
    view_pdg_tutor_location.locality as Locality,
    view_pdg_tutor_location.address as Address,
    view_pdg_batch_class_timing.class_timing as Class_Timing

    from view_pdg_tuition_batch

    left join view_pdg_institutes on (view_pdg_tuition_batch.tutor_institute_user_id = view_pdg_institutes.user_id)
    left join view_pdg_batch_subject on (view_pdg_batch_subject.tuition_batch_id = view_pdg_tuition_batch.tuition_batch_id)
    left join view_pdg_batch_fees on (view_pdg_batch_fees.tuition_batch_id = view_pdg_tuition_batch.tuition_batch_id) 
    left join view_pdg_batch_class_timing on (view_pdg_batch_class_timing.tuition_batch_id = view_pdg_tuition_batch.tuition_batch_id)
    left join view_pdg_tutor_location on (view_pdg_tutor_location.tuition_batch_id = view_pdg_tuition_batch.tuition_batch_id)
group by view_pdg_tuition_batch.tuition_batch_id;

I need a solution that would not require changing the current approach of writing the query.

Copyright Notice:Content Author:「Piyush Das」,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/37745471/mysql-can-only-use-61-tables-in-a-join-how-can-i-bypass-this-error-for-the-foll

Answers
polkovnikov.ph 2016-06-10T12:45:04

I don't think it's possible to do what you're asking without some elaborate changes in the way you store and query data. You can\n\n\ndenormalize your DB to store JSON data;\ncreate materialized views, emulating them via triggers, because they're absent in MySQL;\nuse temporary tables;\njoin partial selects by hand at the call site;\ncompile MySQL with another join limit;\nuse proper SQL engine like Postgres, that doesn't suffer from such stupid things.\n",


More about “MySQL can only use 61 tables in a join; How can I bypass this error for the following query?” related questions

MySQL can only use 61 tables in a join; How can I bypass this error for the following query?

I get a MySQL Error saying, I cannot use more than 61 tables in a join. I need to avoid this error. How do I do it? Please Help. select view_pdg_institutes.user_id as User_ID,

Show Detail

Vtiger Report generation failed! Too many tables; MySQL can only use 61 tables in a join

Hello I am usign VTiger CRM version 5.1 and when a user i trying to generate Report called "Last Month activities" in the result area only following error is given: Report generation failed! Too m...

Show Detail

How to join more than 61 tables in mysql?

In my drupal application, query is generated dynamically by joining more than 61 tables. But while executing the query i got a error like cannot join more than 61 tables. I cannot reduce the number...

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

Too many tables; MySQL can only use 61 tables in a join

What is the best way to export data from multiple tables in MySQL. I'm basically working with product details. Say a product has 150 attributes of data. How can I export that in a single row and t...

Show Detail

java.sql.SQLTransientConnectionException: (conn=15) Too many tables; MariaDB can only use 61 tables in a join

I'm a beginner with JPA, and whilst accessing the database from my DAO, an error stating MariaDB being limited to 61 tables in a join pops up. Here's a portion of an Entity's code @Entity @Table(na...

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 to LEFT JOIN more than 61 tables pup MySQL

I generated a large data query (one query ) more than 61 of type LEFT JOIN and now I can't continue how to solve this problem I need to add more tables. My query is as follows: <?php require_once '

Show Detail

Can I use temporary tables to improve query performance of mysql

I need to query data from UserMessage table, and need to get the message's user value from UserAccount table. Under normal circumstances, we can use the following SQL to query. select * from UserM...

Show Detail

How many tables can I join together with MySQL?

How many tables can I join together with MySQL? So far I have only been joining 3 tables. What's the max for this?

Show Detail