CODEWITHSUNDEEP
mysqlindexing
Spredzy
Spredzy

Reputation: 5174

MySQL Index issue when dealing with function

I am doing two queries

SELECT * FROM datedim WHERE year = YEAR(now());

and

SELECT * FROM datedim WHERE year = YEAR(getNow());

Here getNow() function just returns NOW();

My problem is that the first one is immediate while the second one take much longer. (1.xx sec)

When I execute the explain command I have this for the first one (which is good)

+----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | datedim | ref  | dd_year_idx   | dd_year_idx | 5       | const |  365 | Using where |
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+

But this for the second one (which is pretty bad)

+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | datedim | ALL  | NULL          | NULL | NULL    | NULL | 10958 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+

Can someone explain me what is happening and why doing the same thing, looking for all date with year that is the same to NOW(), takes much longer when using a function than giving it straight.

This example is simple but later my function will return a specific date, I would like just to understand what is happening.

Upvotes: 2

Views: 96

Answers (3)

newtover
newtover

Reputation: 32094

MySQL seems to execute the getNow() function for each row, NOW() is executed only once per statement:

NOW() returns a constant time that indicates the time at which the statement began to execute. (Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.) This differs from the behavior for SYSDATE(), which returns the exact time at which it executes.

But you can change your query to the following to have the same effect:

SELECT *
FROM datedim
JOIN (SELECT YEAR(getNow()) as `year`) as y
USING (`year`);

Upvotes: 3

Tudor Constantin
Tudor Constantin

Reputation: 26861

My assumption is that MySQL knows that it can evaluate YEAR() and now() functions to some constant value, so after evaluating them (before executing the query), it can use the index dd_year_idx.

MySQL can't do the same thing with your custom function

Upvotes: 0

piotrekkr
piotrekkr

Reputation: 3206

MySQL scan every result to match where condition because when you use user defined function mysql is not using indexes.

Upvotes: 0

Related Questions