Little MySQL optimization/weird behaviour

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:

WHERE AND (date BETWEEN '2015-10-24T15:00:00+00:00' AND '2015-10-24T15:00:00+00:00')
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.

This entry was posted in Knowledge and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s