Sorry, but this is a very bad advise! I suppose you never worked with larger amount of data.
If you use WHERE DATE(created_at) in your query, the database can not use the index for created_at (which hopefully is there), because you are applying a function to the column.
Your approach will work for tables with 100 or 1000 rows because every data is in memory anyway, but if there are like 100 million rows, the performance of the query will be very poor and the load for the database server will be very high!
As a software developer it is important to know how database systems are working, especially what indexes are and how they can make queries faster.
Hi !
Thanks I was actually of investigating this after someone asked me about performance and that's completely true. In my tests I forgot to add an index so the there was no difference.
The thing is, if you know you'll only fetch between two date and not two times you could add a functional index on DATE(created_at) and I would have exepected it to be a little bit faster as it will be smaller and have a lower cardinality.
So far I have found two things :
It's very close but not quite as fast with the functional index, I suspect maybe it's stored as a string compared to the timestamps that are stored as ints ? Or maybe functional indexes are inherently slower ?
For some reason, the functional index is completely useless on my setup with prepared statements ! It works fine if I run the query directly on MySQL, it runs fine in Laravel if I use PDO::ATTR_EMULATE_PREPARES to emulate prepared statements, but as soon as I use native prepared statements I'm back to the same performance as without the index.
The next step (but I ran out of time for now) is to run the prepared statements in the SQL console to determine if it's linked to something Laravel does or not, and if not check what's happening with MySQL. I didn't find anything indicating that functional indexes could not be used with prepared statements so it might be a bug ?
Some comments have been hidden by the post's author - find out more
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
Sorry, but this is a very bad advise! I suppose you never worked with larger amount of data.
If you use WHERE DATE(created_at) in your query, the database can not use the index for created_at (which hopefully is there), because you are applying a function to the column.
Your approach will work for tables with 100 or 1000 rows because every data is in memory anyway, but if there are like 100 million rows, the performance of the query will be very poor and the load for the database server will be very high!
As a software developer it is important to know how database systems are working, especially what indexes are and how they can make queries faster.
Hi !
Thanks I was actually of investigating this after someone asked me about performance and that's completely true. In my tests I forgot to add an index so the there was no difference.
The thing is, if you know you'll only fetch between two date and not two times you could add a functional index on
DATE(created_at)and I would have exepected it to be a little bit faster as it will be smaller and have a lower cardinality.So far I have found two things :
PDO::ATTR_EMULATE_PREPARESto emulate prepared statements, but as soon as I use native prepared statements I'm back to the same performance as without the index.The next step (but I ran out of time for now) is to run the prepared statements in the SQL console to determine if it's linked to something Laravel does or not, and if not check what's happening with MySQL. I didn't find anything indicating that functional indexes could not be used with prepared statements so it might be a bug ?