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.
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 ?