多JOIN慢SQL查询优化求助：单JOIN多条件方案失效问题

Hey there, let's fix that sluggish query and figure out why your initial single JOIN attempt didn't work.

First, Why Your Single JOIN Failed #

Your attempt to combine multiple feature_id checks into a single JOIN with AND conditions was logically flawed:

AND `of1`.`feature_id` = 1 AND `of1`.`feature_id` = 24

A single row in object_features can't have a feature_id equal to both 1 and 24 at the same time. This condition will always return 0 rows, which is why your query didn't work.

Better Optimization Approaches #

Since you need to find objects that have all specified features, here are two efficient alternatives to the repeated JOIN pattern:

Option 1: Use a Filtered Subquery with GROUP BY + HAVING

This approach first narrows down the object_ids that have all required features, then joins back to the objects table. It avoids the intermediate table bloat caused by multiple JOINs.

SELECT SQL_CALC_FOUND_ROWS null as rows, `o`.`id` 
FROM `objects` AS `o` 
LEFT JOIN `geo_regions` AS `r` ON `r`.`id` = `o`.`region_id` 
INNER JOIN (
    SELECT `object_id`
    FROM `object_features`
    WHERE `feature_id` IN (1, 17, 19, 20, 24, 26, 3, 4, 8, 15, 14, 9, 5, 7)
    GROUP BY `object_id`
    -- Match the count to the number of unique features you're checking (14 here)
    HAVING COUNT(DISTINCT `feature_id`) = 14
) AS `of_filter` ON `of_filter`.`object_id` = `o`.`id`
WHERE `object_type` = 1 
  AND `r`.`country` = '3' 
  AND (o.region_id = 20 OR o.region_id_2 = 20) 
  AND (o.city_id = 1175 ) 
  AND `o`.`status` = 1 
  AND `o`.`type` IN('1', '0', '2') 
  AND `o`.`max_persons` >= '8' 
  AND `o`.`animals` = '1' 
ORDER BY `o`.`all_ranking` DESC

• Why this works: The subquery groups features by object_id and ensures the object has all 14 required features (the DISTINCT handles any duplicate feature entries for the same object).
• Index Tip: Make sure object_features has a composite index on (feature_id, object_id) to speed up the subquery's filtering and grouping.

Option 2: Use Multiple EXISTS Subqueries

This method checks for each feature individually using EXISTS, which avoids JOINs entirely and leverages index lookups directly. It's often very fast when you have proper indexing.

SELECT SQL_CALC_FOUND_ROWS null as rows, `o`.`id` 
FROM `objects` AS `o` 
LEFT JOIN `geo_regions` AS `r` ON `r`.`id` = `o`.`region_id` 
WHERE `object_type` = 1 
  AND `r`.`country` = '3' 
  AND (o.region_id = 20 OR o.region_id_2 = 20) 
  AND (o.city_id = 1175 ) 
  AND `o`.`status` = 1 
  AND `o`.`type` IN('1', '0', '2') 
  AND `o`.`max_persons` >= '8' 
  AND `o`.`animals` = '1'
  -- Check for each required feature
  AND EXISTS (SELECT 1 FROM `object_features` WHERE `object_id` = `o`.`id` AND `feature_id` = 1)
  AND EXISTS (SELECT 1 FROM `object_features` WHERE `object_id` = `o`.`id` AND `feature_id` = 17)
  AND EXISTS (SELECT 1 FROM `object_features` WHERE `object_id` = `o`.`id` AND `feature_id` = 19)
  AND EXISTS (SELECT 1 FROM `object_features` WHERE `object_id` = `o`.`id` AND `feature_id` = 20)
  AND EXISTS (SELECT 1 FROM `object_features` WHERE `object_id` = `o`.`id` AND `feature_id` = 24)
  AND EXISTS (SELECT 1 FROM `object_features` WHERE `object_id` = `o`.`id` AND `feature_id` = 26)
  AND EXISTS (SELECT 1 FROM `object_features` WHERE `object_id` = `o`.`id` AND `feature_id` = 3)
  AND EXISTS (SELECT 1 FROM `object_features` WHERE `object_id` = `o`.`id` AND `feature_id` = 4)
  AND EXISTS (SELECT 1 FROM `object_features` WHERE `object_id` = `o`.`id` AND `feature_id` = 8)
  AND EXISTS (SELECT 1 FROM `object_features` WHERE `object_id` = `o`.`id` AND `feature_id` = 15)
  AND EXISTS (SELECT 1 FROM `object_features` WHERE `object_id` = `o`.`id` AND `feature_id` = 14)
  AND EXISTS (SELECT 1 FROM `object_features` WHERE `object_id` = `o`.`id` AND `feature_id` = 9)
  AND EXISTS (SELECT 1 FROM `object_features` WHERE `object_id` = `o`.`id` AND `feature_id` = 5)
  AND EXISTS (SELECT 1 FROM `object_features` WHERE `object_id` = `o`.`id` AND `feature_id` = 7)
ORDER BY `o`.`all_ranking` DESC

• Why this works: Each EXISTS does a quick index lookup to verify the feature exists for the object. There's no intermediate table creation, so it's lightweight.
• Index Tip: A composite index on object_features of (object_id, feature_id) will make these EXISTS checks almost instantaneous.

Bonus Performance Tip #

If you're using MySQL 8.0.17 or later, SQL_CALC_FOUND_ROWS is deprecated and can add unnecessary overhead. Instead, run your paginated query first, then execute a separate COUNT(*) query (without LIMIT) to get the total number of matching rows.

内容的提问来源于stack exchange，提问作者MarkusHH