假设您有5个表格,每个表格都有一列：

>房屋(编号,名称,街道编号)
>街道(ID,名称)
>照片(身份证,姓名)
> house_photo(house_id,photo_id)
> street_photo(street_id,photo_id)

并说所有“ id”列和以“ _id”结尾的列都已经有索引.

(实际上,我的查询与房屋或街道无关.这是出于争论的缘故.)

现在说您想将每条街道都分成一列,如果该街道或其房屋上有照片,则希望在下一列中使用这些照片…

棘手的是,所有房屋都在一张桌子上.和所有照片在另一个表中.但是要链接2,我们需要访问所有五个表.

我提出了以下查询,其中包含4个JOIN：

SELECT
    street.name
    ,group_concat(distinct photos.name SEPARATOR '\n') as photos
FROM
    house
    INNER JOIN street ON
        house.street_id = street.id
    LEFT JOIN house_photos ON
        house.id = house_photos.house_id
    LEFT JOIN street_photos ON
        street.id = street_photos.street_id
    LEFT JOIN photos ON
        photos.id = house_photos.photo_id
        OR photos.id = street_photos.photo_id
GROUP BY
    street.name

独特之处在于可以滤除双打图像,因为当您为一所房子拍摄多于一张照片,而对于该房子的街道有多于一张照片时,将生成双打图像. (笛卡尔积)但这与我的问题无关.

我的问题是查询速度超级慢. (花费1分钟甚至更长的时间才能完成)

当我要求MySQL分析查询(“解释扩展”)时,我看到它在处理最后一个JOIN(在ON子句中具有OR)时将不使用可用索引.

如果我将最后一个JOIN分成2个JOINS(因此又增加了第5个JOIN),查询将再次变得非常快(花一秒钟完成).

SELECT
    street.name
    ,concat(
        group_concat(distinct photos_from_house.name SEPARATOR '\n')
        ,'\n'
        ,group_concat(distinct photos_from_street.name SEPARATOR '\n')
    ) as photos
FROM
    house
    INNER JOIN street ON
        house.street_id = street.id
    LEFT JOIN house_photos ON
        house.id = house_photos.house_id
    LEFT JOIN street_photos ON
        street.id = street_photos.street_id
    LEFT JOIN photos photos_from_house ON
        photos_from_house.id = house_photos.photo_id
    LEFT JOIN photos photos_from_street ON
        photos_from_street.id = street_photos.photo_id
GROUP BY
    street.name

我现在的问题是：为什么在ON子句中引入OR,使MySQL不使用该JOIN的可用索引/键？

我已经尝试使用USE INDEX和FORCE INDEX,但是不会让步.

欢迎任何解释/帮助.

解决方法:

The MySQL docs这样说：

Minimize the OR keywords in your WHERE clauses. If there is no index
that helps to locate the values on both sides of the OR, any row could
potentially be part of the result set, so all rows must be tested, and
that requires a full table scan. If you have one index that helps to
optimize one side of an OR query, and a different index that helps to
optimize the other side, use a UNION operator to run separate fast
queries and merge the results afterward.

不幸的是,这并不能真正回答您的问题,因为您说在所有相关列上都有索引.