php - MySQL查询优化避免文件排序

我希望有人帮助我优化和避免在下面的查询文件排序。
附上解释的截图

SELECT DISTINCT(storages.id),
     IF(admin_approve = 1
           OR storage_free_auctions.user_id = 167
           OR ISNULL(storage_free_auctions.user_id),
        auction_date, NULL) AS auction_date1
FROM user_list_storage
JOIN storages ON storages.id = user_list_storage.storage_id
JOIN states ON states.id = storages.state
LEFT JOIN storage_auctions ON storage_auctions.id = (
          SELECT storage_auctions.id FROM storage_auctions
          LEFT JOIN storage_free_auctions
          ON storage_free_auctions.storage_auctions_id=storage_auctions.id
          WHERE storage_auctions.storage_id = storages.id
          AND storage_auctions.status = 'Active'
          AND (  ISNULL(storage_free_auctions.user_id)
                 OR admin_approve = 1
                 OR storage_free_auctions.user_id = 167)
          AND CONCAT(auction_date,' ',start_time) >= CURDATE()
          ORDER BY auction_date ASC LIMIT 1)
LEFT JOIN storage_free_auctions
     ON storage_free_auctions.storage_auctions_id=storage_auctions.id
LEFT JOIN storage_auction_units
     ON storage_auction_units.storage_auction_id = storage_auctions.id
     AND storage_auction_units.status='Active'
WHERE storages.storage_status = 'Active'
AND user_list_storage.user_id = 167
AND user_list_storage.user_list_id = 3
AND (storage_free_auctions.user_id = 167
     OR admin_approve = 1
     OR ISNULL(storage_free_auctions.user_id)
     OR user_list_storage.user_id = 167)
GROUP BY storages.id
order by auction_date1

解释结果的第一行如下。
编号：1
选择类型：主要
表：用户列表存储
类型：ref
可能的密钥：idx_user_list_id，idx_storage_id，idx_user_id
密钥：idx_user_id
基伦：5
参考号：const
行数：64
额外：使用where；使用temporary；使用文件排序

最佳答案

替换

 LEFT JOIN storage_auctions
        ON storage_auctions.id= (SELECT storage_auctions.id
            FROM storage_auctions
            LEFT JOIN storage_free_auctions
                ON storage_free_auctions.storage_auctions_id = storage_auctions.id
            WHERE storage_auctions.storage_id = storages.id
              AND storage_auctions.status = 'Active'
              AND (ISNULL(storage_free_auctions.user_id)
                    OR admin_approve = 1
                    OR storage_free_auctions.user_id = 167)
            AND CONCAT(auction_date,' ',start_time) >= CURDATE() ORDER BY auction_date ASC LIMIT 1)

具有

AND EXISTS (SELECT 1
    FROM storage_auctions
    WHERE storage_auctions.id = storages.id
      AND CONCAT(auction_date,' ',start_time) >= CURDATE())

因为左边连接中的子句是冗余的，并且之前已经完成了，所以您只希望将来有一个具有相同ID的记录，对吗？
其余的人都没事。它们都基于整数列，如果不是主键和runstats，请确保对所有这些id进行索引。

关于php - MySQL查询优化避免文件排序，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/9480090/