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:

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.

Advertisements
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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s