CODEWITHSUNDEEP
phpmysqlperformance
user1016265
user1016265

Reputation: 2397

MySQL DATETIME functions beauty vs perfomance (speed)

How much faster (in %) sql will be if I will avoid to used built-in mysql date and time functions ?

What do I mean ? For example: SELECT id FROM table WHERE WEEKOFYEAR(inserted)=WEEKOFYEAR(CURDATE())

MySQL has a lot of buil-in function to work with date and time, and they are suitable as well. But what about peromance ?

Above sql can be rewritten without built-in functions, like: SELECT id FROM table WHERE inserted BETWEEN 'date for 1 day of particular week 00:00:00' AND 'last day of particular week 23:59:59', server side code become worse :( but on db side we could use indexes

I see two problems for usage built-in functions: 1. indexes

I did small test

mysql> explain extended select id from table where inserted between '2013-07-01 00:00:00' and '2013-07-01 23:59:59';
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | table  | range | ins           | ins  | 4       | NULL |    7 |   100.00 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------+

mysql> explain extended select id from table where date(inserted)=curdate();
+----+-------------+-------+-------+---------------+------+---------+------+--------+----------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | table  | index | NULL          | ins  | 4       | NULL | 284108 |   100.00 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+--------+----------+--------------------------+

First one took 0.00 sec second one was running after first one and took 0.15. Everything was made with small anout of data.

and second problem, is

  1. time to call that functions

If in table I have 1 billion records it means that WEEKOFYEAR, DATE whatever... would be called so many times, so many records do we have, right ?

So the question will it bring real profit if I will stop to work with mysql built-in date and time functions ?

Upvotes: 2

Views: 737

Answers (2)

WayneC
WayneC

Reputation: 5740

The second query is running the date function on every row in the table, while the first query can just use the index to find the rows it needs. Thats where the biggest slowdown would be. Look at the rows column in the explain output

Upvotes: 0

RandomSeed
RandomSeed

Reputation: 29759

Using a function of a column in a WHERE clause or in a JOIN condition will prevent the use of indexes on the column(s), if such indexes exist. This is because the raw value of the column is indexed, as opposed to the computed value.

Notice the above does not apply for a query like this:

SELECT id FROM atable WHERE inserted = CURDATE(); -- the raw value of "inserted" is used in the comparison

And yes, on top of that, the function will be executed for each and every row scanned.

Upvotes: 1

Related Questions