Is it slow to use the PHP date() func to compare with SQL datetime fields?

Question

I have this website where I a user can create so many posts a day called 'clippets' and before I check the limit, I've used some SQL to get the total number of 'clippets' in the current day.

So let's say I only want to allow users to make 20 'clippets' a day, not based on time though only the day, as if I were to base it on the last 24 hours it would be like the user is chasing the limit all the time...

Anyway, here is my SQL:

$date = date("Y-m-d");

$sql = "SELECT COUNT(id) FROM clippets WHERE userID = ? AND DATE(createdAt) = '$date'; ";

My question is... Is this a slow way to approach this, using the PHP date function and then using that within the SQL query, does it treat them as strings or? Because the createdAt field is a datetime data type, so does MySQL convert both dates to strings and then compare? Is that a slow way of doing this?

Why not testing it by yourself? Use microtime() before and after each case and comparing them? — Ofir Baruch, Nov 02 '17 at 15:36
@YvesLeBorg I meant that he should use microtime() in both cases in order to test the efficiency of the his code. Or you're being sarcastic? — Ofir Baruch, Nov 02 '17 at 15:43
But with createdAT being a datetime in the DB and date() being a string, what happens, does the DB convert both to strings or...? — Erdss4, Nov 02 '17 at 15:44
@Erdss4 I think you should just try to do some test by yourself. SO is for having help when you tried and get stuck. — Lucarnosky, Nov 02 '17 at 15:47
@OfirBaruch misguided sarcasm, that all. Could be slower than `nanotime()` though :) — YvesLeBorg, Nov 02 '17 at 16:19
The meddle of jokes, sarcasm, indirection, premature optimization and misguided helpfulness of these few comments is amazing. — Félix Adriyel Gagnon-Grenier, Nov 23 '17 at 20:14

Don't Panic · Answer 1 · 2017-11-02T15:53:32.357

MySQL does not convert both dates to strings for the comparison. It converts the string to a date once, and uses that for all the other comparisons. Passing a date parameter as a string is standard practice and trying to do something else instead will not be a good source of optimization.

However, if you're only interested in the current date, you don't need to pass a parameter for the date at all. You can use MySQL date functions to select the rows you need. One of the approaches in this question or this question should work.

For example:

SELECT COUNT(id) FROM clippets WHERE userID = ? AND createdAt >= CURDATE()

score 0 · Answer 2 · answered Nov 02 '17 at 16:14

As a rule, filtering on function results is slower than filtering on fields, particularly so with indexed field. If your datatype includes the time, your filter should resemble this:

where createdAt >= yourDateVariable
and createdAt < the day after yourDateVariable

Is it slow to use the PHP date() func to compare with SQL datetime fields?

2 Answers2