我正在运行一个查询,该查询需要5分钟以上的时间才能产生结果。当我将LEFT JOIN中的DATE(NOW))更改为一列时,它需要9秒钟。下面是显示更快的行的查询
SELECT DISTINCT(rental.id), filmCopy.location fC_id, member.id m_id, filmInfo.title fI_title, member.name, member.surname, member.cellphone, member.telephone, rental.due_back,rental.returned, filmCopy.on_loan_to, filmInfo.folder fI_folder, reservation_date rsrvtn, reservation.id res_id
FROM rental
INNER JOIN transactionSummary ON transactionSummary.id = rental.transactionSummary_id
INNER JOIN member ON member.id = transactionSummary.member_id
INNER JOIN filmCopy ON filmCopy.id = rental.filmCopy_id
INNER JOIN filmInfo ON filmInfo.id = filmCopy.filmInfo_id
INNER JOIN filmPriceBracket ON filmPriceBracket.filmInfo_id = filmInfo.id
AND filmPriceBracket.filmCopytype_id = filmCopy.filmCopyType_id
LEFT JOIN reservation ON reservation.filmInfo_id = filmInfo.id
快得多-保留JOINT上的LEFT JOIN预订.filmInfo_id = filmInfo.id并且
Reservation.reservation_date = DATE(rental.due_back)
AND reservation.reservation_date = DATE(NOW())
WHERE rental.due_back < DATE_SUB(NOW(), INTERVAL 1 DAY)
AND rental.returned IS NULL
AND filmPriceBracket.filmCopyType_id !=24
AND filmPriceBracket.filmCopyType_id !=23
ORDER BY fI_folder, rsrvtn DESC, filmCopy_id, rental.due_back
慢速查询可以为我提供正确的结果,而快速查询可以但不完整。
关于为什么使用今天的DATE如此缓慢的任何建议?
对于背景信息,它会提取已过期和已过期电影的报告(过期和未过期)
谢谢
编辑
这是我从以下答案中得出的结论,但语法有误。
DECLARE @NOW DATE
SELECT @NOW := DATE(NOW())
SELECT DISTINCT(rental.id), filmCopy.location fC_id, member.id m_id, filmInfo.title fI_title, member.name, member.surname, member.cellphone, member.telephone, rental.due_back,rental.returned, filmCopy.on_loan_to, filmInfo.folder fI_folder, reservation_date rsrvtn, reservation.id res_id
FROM rental
INNER JOIN transactionSummary ON transactionSummary.id = rental.transactionSummary_id
INNER JOIN member ON member.id = transactionSummary.member_id
INNER JOIN filmCopy ON filmCopy.id = rental.filmCopy_id
INNER JOIN filmInfo ON filmInfo.id = filmCopy.filmInfo_id
INNER JOIN filmPriceBracket ON filmPriceBracket.filmInfo_id = filmInfo.id
AND filmPriceBracket.filmCopytype_id = filmCopy.filmCopyType_id
LEFT JOIN reservation ON reservation.filmInfo_id = filmInfo.id
AND reservation.reservation_date = @NOW
WHERE $where
AND rental.returned IS NULL
AND filmPriceBracket.filmCopyType_id !=24
AND filmPriceBracket.filmCopyType_id !=23
ORDER BY fI_folder, rsrvtn DESC, filmCopy_id, rental.due_back
最佳答案
您可以尝试先创建两个包含DATE(NOW())
和DATE_SUB(...)
值的变量,这样就不必为每个记录都对它们进行求值。
在MySQL中应该执行以下操作:
DECLARE @NOW DATE;
DECLARE @YESTERDAY DATE;
SELECT @NOW := DATE(NOW());
SELECT @YESTERDAY := DATE_SUB(@NOW, INTERVAL 1 DAY);
...
AND reservation.reservation_date = @NOW
WHERE rental.due_back < @YESTERDAY
...
关于mysql - DATE()导致SQL查询花费太长时间,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/20066638/