MYSQL - Pagination query execution time issue

Question

I am trying to do pagination in Coldfusion, with MySql, to retrieve 20 records at a time. First, I get the total count of records of what I want to retrieve.

<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>

Then I use the following to retrieve 20 records at a time:

<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>

(Note: the value of #start# will increment by 20 for each page.)

Everything seems to work fine. When I clicked on the first page, the execution time (#tmpResult.ExecutionTime#) indicated that it took 40 (I assume it is milliseconds) to retrieve 20 records. However, if I click on the last page, which could be record number 40,000, it takes 500 milliseconds to retrieve last 20 records. Going backward it seems that those later pages took a longer time to retrieve.

Why the discrepancy? Shouldn't retrieving the same amount of 20 records take the same amount of time whether is it from the "front" or the "back"? Am I missing something here? Thanks in advance.

I've been drinking so I might be missing something, but it looks to me that execution time is the least of your worries. Your second query looks like it will return the same 20 records every single time, unless the data changes. — Dan Bracuk, Nov 06 '17 at 02:50
@DanBracuk Sorry, that is not the complete code. The value of the #start# will increment depending on the page number. I will add a bit more explanation. — Jack, Nov 06 '17 at 02:53
You are not likely to get equal performance for every page, it is being sorted, and then it has to reach the first line to be returned. These are NOT "free of time & effort" in fact the opposite is true, sorting and paginating based on the sort are expensive and time consuming. — Paul Maxwell, Nov 06 '17 at 03:07
@Used_By_Already Every iteration have the same sorting. It's just matter of retrieving the first 20 or the last 20 records. I just don't get why getting the "last" 20 records always takes a lot longer. — Jack, Nov 06 '17 at 03:30
Possible duplicate of [Why does MYSQL higher LIMIT offset slow the query down?](https://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down) — Twillen, Nov 06 '17 at 13:48

score 0 · Answer 1 · answered Nov 08 '17 at 23:00

First read and study my blog on why Pagination via Offset is bad . Note that it explains a way to make it much faster.

If the initial COUNT(*) is likely to be slow, rethink the UI. Notice how search engines no longer tell you how many items were found, or at least give you only an approximate answer? Take a hint from the lesson they learned long ago.

To address your question of "why"... LIMIT 100, 20 must find 120 rows, skip 100, then deliver 20. So the pages get slower and slower.

But it gets worse. If the query is too complex, it must find all the matching rows, sort all of them, and only then do the offset and limit. In this situation, even the first page is slow when there is a large number of rows.

MYSQL - Pagination query execution time issue

1 Answers1