尽管有LIMIT，但MySQL的自定义函数查询速度非常慢 [英] MySQL very slow query with custom function in spite of LIMIT

问题描述

我有一张下表

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)

现在，我想获得最后15行(按时间戳排序)，并且我也希望得到MY_FUNC的结果.我试过了:

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;

但是，此查询执行时间非常长(当表具有> 5000行时，此查询将执行2秒).

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

我想出了一个办法:

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

可以工作，并且可以快速工作(对于同一张表，<0.1s).但是我不喜欢这种解决方案.

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

我想在第一个查询中，MySQL为每条记录运行MY_FUNC，而不仅是我要查询的15条记录.我对吗?有没有一种方法可以配置MySQL/查询，以便仅针对选定的行计算该函数?尽管这些结果没有用，为什么MySQL会为所有这些计算呢?

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?

我知道，如果在WHERE或ORDER子句中使用它们，它们也不会无用.但事实并非如此.

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

推荐答案

MySQL为什么要为所有行计算函数?这就是数据库的工作方式(而不仅仅是MySQL).在处理SELECT，FROM，WHERE，GROUP BY和HAVING子句之后，将ORDER BY子句应用于结果集.为了创建结果集，必须为所有行处理值. LIMIT应用于ORDER BY的结果.

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;

这不是"hack"，而是解决问题的非常合理的方法.该参数更改了MySQL和其他所有数据库引擎进行此处理的方式.那将是一个hack.

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.

请注意:如果没有索引，在myTable(creationTime)上的索引将有助于此查询的性能.

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

这篇关于尽管有LIMIT，但MySQL的自定义函数查询速度非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！