CODEWITHSUNDEEP
phpmysql
David19801
David19801

Reputation: 11448

date_add or date_sub speed difference?

Given the following two mysql queries:

SELECT * FROM table1 WHERE DATE_ADD(datetimecolumn,INTERVAL 24 HOUR)>NOW()

And

SELECT * FROM table1 WHERE DATE_SUB(NOW(),INTERVAL 24 HOUR)>datetimecolumn

Which query will be faster?

Upvotes: 2

Views: 1527

Answers (2)

LSerni
LSerni

Reputation: 57418

What I think you want to know:

The second ought to be appreciably faster, since datetimecolumn may be directly used in an index operation. Without an index, I'd say the difference ought to be negligible (time operation and function calls being too much faster than data retrieval from disk).

What you really asked:

The two queries are not equivalent (they do not return the same records).

DATE_ADD(datetimecolumn,INTERVAL 24 HOUR)>NOW()

retrieves the records NEWER than 24 hours from now (so, all those inserted since yesterday).

The second

DATE_SUB(NOW(),INTERVAL 24 HOUR)>datetimecolumn

retrieves the records OLDER than 24 hours from now (two or more days in the past).

So in that case, which query is the faster, the answer would be it depends on how many records there are...

Upvotes: 2

xdazz
xdazz

Reputation: 160883

The below will be fast.

The below one could apply the index of datetimecolumn, while the above one could not.

DATE_SUB(NOW(),INTERVAL 24 HOUR) only need to be calculated once, while DATE_ADD(datetimecolumn,INTERVAL 24 HOUR) need to do with every records.

Upvotes: 1

Related Questions