MYSQL -分页查询执行时间问题

Question

我正在尝试使用MySql在Coldfusion中进行分页，以便一次检索20条记录。首先，我得到我想要检索的记录的总数。

<CFQUERY DATASOURCE="#datasource#" USERNAME="#username#" PASSWORD="#password#" NAME="myCount" CACHEDWITHIN="#CreateTimeSpan(0,0,3,0)#">
   SELECT COUNT(*) as TotalCount
   FROM products
   WHERE products.field1 = <cfqueryparam value = "#myfield#" cfsqltype = "cf_sql_integer" maxLength = "2">
   AND MATCH(field2) AGAINST(<cfqueryparam value = "#TRIM(string)#" cfsqltype = "cf_sql_char" maxLength = "52"> IN BOOLEAN MODE)
</CFQUERY>      

然后，我使用以下代码一次检索20条记录：

<CFQUERY DATASOURCE="#datasource#" USERNAME="#username#" PASSWORD="#password#" NAME="myQuery" result="tmpResult">
    SELECT products.field1, products.field2, products.field3, company.field1, company.field2
    FROM products JOIN company ON products.field1 = company.field1
    WHERE products.field2 = <cfqueryparam value = "#myfield#" cfsqltype = "cf_sql_integer" maxLength = "2">
    AND MATCH(field2) AGAINST(<cfqueryparam value = "#TRIM(string)#" cfsqltype = "cf_sql_char" maxLength = "52"> IN BOOLEAN MODE)
    ORDER by products.field3 DESC
    LIMIT <cfqueryparam value="#start#" cfsqltype="cf_sql_integer">, 20
</CFQUERY>

(注意:每页#start#的值会递增20。)

一切似乎都很正常。当我单击第一个页面时，执行时间(#tmpResult.ExecutionTime#)显示检索20条记录花费了40分钟(我假设是毫秒)。但是，如果我单击最后一页，它可能是记录号40,000，检索最后20条记录需要500毫秒。回过头来看，那些较晚的页面似乎花费了更长的时间来检索。

为什么会有这样的差异？无论是从“正面”还是“背面”检索相同数量的20条记录，不应该花费相同的时间吗？我是不是漏掉了什么？提前谢谢。

Answer 1

首先阅读和研究我的。请注意，它解释了一种使其更快的方法。

如果初始COUNT(*)可能会很慢，请重新考虑UI。注意到搜索引擎不再告诉你找到了多少项了吗，或者至少只给了你一个大概的答案？从他们很久以前学到的教训中得到一些提示。

来回答你的“为什么”的问题...LIMIT 100, 20必须找到120行，跳过100行，然后交付20行，所以页面变得越来越慢。

但情况会变得更糟。如果查询太复杂，它必须找到所有匹配的行，对所有行进行排序，然后才能进行偏移和限制。在这种情况下，当有大量行时，即使是第一页也很慢。