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

Question

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.

What does the message says, exactly? There's certainly less than 61 tables in this query. — polkovnikov.ph, Jun 10 '16 at 10:37
I am querying data from Views. So maybe it is dismantling the view and querying individual tables from which the views are constructed. That makes it more than 61. — Piyush Das, Jun 10 '16 at 11:17
"I need a solution that would not require changing the current approach of writing the query" - then what do you expect us to do? If we can't change the current query, and the current query is running into a hard limit, what *can* we change. We're not able to just wave a magic wand and say "carry on". — Damien_The_Unbeliever, Jun 10 '16 at 12:50

score 1 · Answer 1 · edited May 23 '17 at 12:24

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

denormalize your DB to store JSON data;
create materialized views, emulating them via triggers, because they're absent in MySQL;
use temporary tables;
join partial selects by hand at the call site;
compile MySQL with another join limit;
use proper SQL engine like Postgres, that doesn't suffer from such stupid things.

score 0 · Answer 2 · answered Jun 10 '16 at 10:52

0

Insert the contents of each view into its own temporary table. Then do the same query with the temporary table names substituted for the original view names.

answered Jun 10 '16 at 10:52

Kevin Anderson

4,568
3
13
21

Does that count as a solution that doesn't require changing the current approach to writing the query? – polkovnikov.ph Jun 10 '16 at 12:36

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

2 Answers2