Here is another little weird behaviour about MySQL (5.5.46-0ubuntu0.14.04.2).
Today i needed to make a query for searching a table with millions of rows for all rows between two dates. The query goes something like this:
SELECT * FROM Tbl1 WHERE Tbl1.id=1 AND (date BETWEEN '2015-10-24T15:00:00+00:00' AND '2015-10-24T15:00:00+00:00') ORDER BY date DESC LIMIT 1000
Running querys like this should be quick, even for millions of rows (with proper indexes obviously), however it was taking several minutes to get the rows.
So where is the problem? Well, you see that strange format for the dates? That is the default output for moment.js’s format(). I’ve been using it to get the start and end dates from the user.
The problem arises from the fact that MySQL does not support that particular date format string (not sure if MariaDB has the same problem) but the solution is simple. Call moment.js’s format() with the correct format string (in my case it was .format(“YYYY-MM-DD HH:mm”)) and all should be good again.
Now, the weird behaviour is that MySQL dosen’t really complain about the invalid date format. It will throw a warning about it (that you may not see if you are using PHP for executing the query, depending on the PDO configuration) but appart from that it will, oddly, return the correct results. It will just take a long time because it will throw a warning for each row that it scans while building the result set.