StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCan several JOIN clauses in a MySQL query exponentiate number of rows it has to check?
text
Body
copied!<p>So I was on tech support with our host provider about slow database response that I'd been experiencing on a crucial search page and after some investigating he told me that the query that I'm using was checking 67,998,176 rows. Now the largest table that appears on this query has <strong>at most 116</strong> rows and the rest of them have an average of 25 rows each.</p> <p>Here's the SQL of my query </p> <pre><code> SELECT COUNT( DISTINCT `A`.`id`) AS `total_num_resource_rows` FROM `admin_site_resources` AS `A` LEFT JOIN `admin_site_organization_resource` AS `B` ON `B`.`res_id`=`A`.`id` LEFT JOIN `admin_site_organizations` AS `C` ON `B`.`org_id`=`C`.`id` LEFT JOIN `admin_site_resource_res_topic` AS `D` ON `D`.`resource_id`=`A`.`id` LEFT JOIN `admin_site_resource_topics` AS `E` ON `E`.`id`=`D`.`res_topic_id` LEFT JOIN `admin_site_resource_audience` AS `F` ON `F`.`resource_id`=`A`.`id` LEFT JOIN `admin_site_audiences` AS `G` ON `G`.`id`=`F`.`audience_id` LEFT JOIN `admin_site_resource_curriculum_topic` AS `H` ON `H`.`resource_id`=`A`.`id` LEFT JOIN `admin_site_curriculum_topics` AS `I` ON `I`.`id`=`H`.`curriculum_topic_id` LEFT JOIN `admin_site_resource_curriculum_grade` AS `J` ON `J`.`resource_id`=`A`.`id` LEFT JOIN `admin_site_curriculum_grades` AS `K` ON `K`.`id`=`J`.`curriculum_grade_id` LEFT JOIN `admin_site_resource_curriculum_subject` AS `L` ON `L`.`resource_id`=`A`.`id` LEFT JOIN `admin_site_curriculum_subjects` AS `M` ON `M`.`id`=`L`.`curriculum_subject_id` LEFT JOIN `admin_site_resource_res_type` AS `N` ON `N`.`resource_id`=`A`.`id` LEFT JOIN `admin_site_resource_types` AS `O` ON `O`.`id`=`N`.`res_type_id` LEFT JOIN `admin_site_resource_res_area_location` AS `P` ON `P`.`resource_id`=`A`.`id` LEFT JOIN `admin_site_resource_area_locations` AS `Q` ON `Q`.`id`=`P`.`res_area_location_id` WHERE `A`.`post_status`='approved' AND ( `A`.`resource_name` LIKE '%alpha%' OR `A`.`aliases` LIKE '%alpha%' OR `A`.`short_desc` LIKE '%alpha%' OR `A`.`resource_url` LIKE '%alpha%' OR `A`.`other_resource_type` LIKE '%alpha%' OR `C`.`org_name` LIKE '%alpha%' OR `E`.`topic_label` LIKE '%alpha%' OR `G`.`audience_label` LIKE '%alpha%' OR `I`.`topic_label` LIKE '%alpha%' OR `K`.`grade_label` LIKE '%alpha%' OR `M`.`subject_label` LIKE '%alpha%' OR `O`.`type_label` LIKE '%alpha%' OR `Q`.`area_location_label` LIKE '%alpha%' ); </code></pre> <p>Yes, I understand this query is pretty long and ugly but I guess it's a result of all the normalization that had to be done for it. What I can't understand is how a few rows suddenly turned to almost 68 million rows. Are the LEFT JOINS to blame for this?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload