CODEWITHSUNDEEP
mysqldate
Eiad Samman
Eiad Samman

Reputation: 407

MySQL date range selection

Known that the following SQL statements will select a data range, which one is the most efficient one among the others, and do using timestamp or datetime will change the performance

Assume we have the table test with columns timestamp, datatime And I want to extract all records for May, 2015:

WHERE DATE_FORMAT(timestamp,'%Y-%m') = '2015-05'
WHERE timestamp BETWEEN '2015-01-01' AND '2015-01-31'
WHERE timestamp >= '2015-01-01' AND timestamp <= '2015-01-31'
WHERE YEAR(timestamp) = 2015 AND MONTH(timestamp) = 5

Which should i avoid, and which is the best practice

Upvotes: 1

Views: 63

Answers (1)

John Ruddell
John Ruddell

Reputation: 25862

This is almost a subjective question because it really depends on what indexes you have on the table and columns.

if you have indexes on your column then you can use either of these two

WHERE timestamp BETWEEN '2015-01-01' AND '2015-01-31'
WHERE timestamp > '2015-01-01' AND timestamp < '2015-01-31'

both of those can utilize the index and perform much faster

as to which of those two will execute faster, I don't think there is a difference. my preference is to use the BETWEEN because it makes more sense to me when reading a query. Pick one and stick with it. Consistency is key

Upvotes: 1

Related Questions