CODEWITHSUNDEEP
sql
Krishanu Dey
Krishanu Dey

Reputation: 6406

Need to optimize a SQL Query

Here is the where clause of the SQL. when I'm removing this part, it's taking 3 mins to execute, with this one it's taking more than 15 mins there are almost 9000 records in DB

WHERE username = 'xyz' AND 
( 
    ( acceptedfordeliverytime BETWEEN '2012-9-1' AND '2013-1-29' ) 
    OR 
    ( 
        YEAR(acceptedfordeliverytime) = YEAR('2013-1-29') 
        AND 
        MONTH(acceptedfordeliverytime) = MONTH('2013-1-29') 
        AND 
        DAY(acceptedfordeliverytime) = DAY('2013-1-29')
    ) 
    OR 
    ( 
        YEAR(acceptedfordeliverytime) = YEAR('2012-9-1') 
        AND 
        MONTH(acceptedfordeliverytime) = MONTH('2012-9-1') 
        AND 
        DAY(acceptedfordeliverytime) = DAY('2012-9-1') 
    )
)

Upvotes: 0

Views: 40

Answers (4)

Ali Adravi
Ali Adravi

Reputation: 22733

Your both OR part is unnecessary.

when you use 

acceptedfordeliverytime  Between '2012-9-1' AND '2013-1-29'

It used both date included so OR part is not needed. if you have date and time in your database then you can convert it into date like this 

CAST(acceptedfordeliverytime as date)  Between '2012-9-1' AND '2013-1-29'

Upvotes: 0

Floris
Floris

Reputation: 46375

Isn't BETWEEN inclusive? This would mean that you don't need all the extra clauses (that check for the first and last date in a VERY elaborate way). I believe your query would return the same result if you just did:

WHERE username = 'xyz' AND 
( 
    acceptedfordeliverytime BETWEEN '2012-9-1' AND '2013-1-29'
)

Upvotes: 0

Jade
Jade

Reputation: 2992

I think this will give you the same results. and also make an index for column acceptedfordeliverytime to speed up searching.

WHERE username = 'xyz' AND acceptedfordeliverytime BETWEEN '2012-9-1' AND '2013-1-29'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Why don't you just use:

WHERE username = 'xyz' AND 
      (acceptedfordeliverytime >= '2012-09-01' AND 
       acceptedfordeliverytime < '2013-01-30'
      )

This will also allow an index on (username, acceptedfordeliverytime) to be used when executing the query.

Upvotes: 2

Related Questions