mysql - MySQL EXISTS()真的很慢

这个查询

SELECT
    itemID,
    locationParentID,
    locationID,
    categoryParentID,
    categoryID,
    itemTitle,
    itemDetails,
    itemAttributes,
    itemPictures,
    itemFeatured,
    itemSpotlight,
    itemAdded
FROM items
WHERE items.siteID IN('".$cfg['site']['siteShares']."')
    AND EXISTS(SELECT 1 FROM sites_locations sl WHERE items.locationID  = sl.locationID AND siteID = '".$cfg['site']['siteID']."' LIMIT 1)
    AND EXISTS(SELECT 1 FROM sites_categories sc WHERE items.categoryID = sc.categoryID AND siteID = '".$cfg['site']['siteID']."' LIMIT 1)
    AND itemStatus = '1'
    AND itemAdded > '".$cfg['timestamp']."'

可以，但是最多需要6秒

我可以使用JOIN做，因为我猜它会更快

这是我尝试使用JOIN的方法，但是它返回所有结果，而不仅仅是返回在sites_location s表中具有位置的项目。

SELECT
    itemID,
    locationParentID,
    categoryParentID,
    categoryID,
    itemTitle,
    itemDetails,
    itemAttributes,
    itemPictures,
    itemFeatured,
    itemSpotlight,
    itemAdded
FROM items LEFT JOIN sites_locations
    ON items.locationID = sites_locations.locationID
    AND sites_locations.siteID = items.siteID
WHERE items.siteID IN('1,2')  AND itemStatus = '1'
    AND itemAdded > '1356048000' ORDER BY itemID DESC LIMIT 15

最佳答案

不知道是否在第二个查询中对表sites_categories进行联接。

关于mysql - MySQL EXISTS()真的很慢，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/15021956/