CODEWITHSUNDEEP
mysqlsql
southafricanrob
southafricanrob

Reputation: 351

DATE( ) is causing SQL query to take way too long

I am running a query which is taking over 5minutes to produce results. When I change the DATE(NOW)) in the LEFT JOIN to a column it takes 9 seconds. Below is the query with the faster line shown 

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

Much faster - LEFT JOIN reservation ON reservation.filmInfo_id = filmInfo.id AND 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

The slow query gives me the correct result wheres the fast one is okay but incomplete. Any suggestions as to why using todays DATE makes it so slow??

For background info it pulls a report of overdue movies as well as reserved (both overdue and not) Thanks

EDIT

Heres what I have trued from the answers below but am getting Error in Syntax..

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

Upvotes: 0

Views: 1102

Answers (3)

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

You may try to create two variable first that contain the values of DATE(NOW()) and DATE_SUB(...) so that they don't have to be evaluated for every record.

Something like this should do in 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
...

Upvotes: 2

LSerni
LSerni

Reputation: 57388

First of all verify that the two query do indeed return the same number of rows (I had this one bite me more than once).

Then, you can avoid DATE(NOW()) being evaluated for each row by doing - this is MySQL syntax; you did not say what DB you were using -

SELECT @DATENOW:=DATE(NOW());
SELECT @DATESUB:=DATE_SUB(NOW(), INTERVAL 1 DAY)

and using @DATENOW and @DATESUB instead of DATE(NOW()) and DATE_SUB(NOW(), INTERVAL 1 DAY).

In general, you want all expressions that yield a constant or almost-constant (NOW() actually varies during the query) to be explicitly made constants. You can also do this application side, if you can build the query programmatically:

# very pseudo code
query := 'SELECT ... ' + date_format(...) + '...'
run_query( query )

Upvotes: 0

zibidyum
zibidyum

Reputation: 174

by writingreservation.reservation_date = DATE(NOW()) you are calling NOW() function for every record that meets the filters you defined on where clause, that is the bottleneck for your sql code.

you can define a variable for DATE(NOW()) condition and use it instead.

note: if you are using oracle system you can use with clause for passing the variable

Upvotes: 0

Related Questions