MySQL 自定义函数设置执行时间,尽管有LIMIT，但MySQL的自定义函数查询速度非常慢...

I have a following table

id INT UNSIGED PRIMARY KEY

attrib1 INT

attrib2 INT

creationTime TIMESTAMP

And I have a function, that calculates very sophisticated result out of the attributes. It can be parametrized by a value from outside, thus I can not store the result as another attribute. Also, the funciton is not deterministic.

MY_FUNC(param1, attrib1, attrib2)

Now I want to get last 15 rows (ordered by timestamp) and I want to have the results of MY_FUNC as well. I tried:

SELECT *, MY_FUNC(2, attrib1, attrib2) FROM myTable ORDER BY creationTime LIMIT 0, 15;

Hovewer, this query is executing very long (> 2s when table has > 5000 rows).

I have come up with a hack:

SELECT *, MY_FUNC(2, attrib1, attrib2) FROM (

SELECT * FROM myTable ORDER BY creationTime LIMIT 0, 15

) AS temp;

Which works, and works fast (< 0.1s for the same table). But I don't like this solution.

I suppose that in the first query MySQL runs MY_FUNC for every record, not only for the 15 I'm querying for. Am I right? Is there a way to configure MySQL/query so the function is calculated only for selected rows? Why MySQL calculates it for all of them although these results are useless?

I understand that they wouldn't be useless if they were used in WHERE or ORDER clause. But they are not.

解决方案

Why does MySQL calculate the function for all rows? That is how databases work (and not just MySQL). The ORDER BY clause is applied to the result set after the SELECT, FROM, WHERE, GROUP BY, and HAVING clauses are processed. In order to create the result set, the values have to be processed for all rows. The LIMIT is applied to the results of the ORDER BY.

You have come across the way to get the performance you want:

SELECT t.*, MY_FUNC(2, attrib1, attrib2)

FROM (SELECT t.*

FROM myTable t

ORDER BY creationTime

LIMIT 0, 15

) t;

This is not a "hack", but a very reasonable approach to solving your problem. A parameter that changed the way that MySQL -- and all other database engines -- do this processing. That would be a hack.

As a note: an index on myTable(creationTime) would help the performance of this query, if you do not have one.