CODEWITHSUNDEEP
mysqlsqldatabase
Piyush Das
Piyush Das

Reputation: 670

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.

Upvotes: 1

Views: 2875

Answers (2)

polkovnikov.ph
polkovnikov.ph

Reputation: 6632

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

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

Upvotes: 1

Kevin Anderson
Kevin Anderson

Reputation: 4592

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.

Upvotes: 0

Related Questions