How to avoid running the same expensive MySql query twice in pagination?

Question

Assume a houses table with lot's of fields, related images tables, and 3 other related tables. I have an expensive query that retrieves all houses data, with all data from the related tables. Do I need to run the same expensive MySql query twice in the case of pagination: once for current result page and once to get the total number of records?

I'm using server-side pagination with Limit 0,10, and need to return the total number of houses along with the data. It doesn't make sense to me to run the same expensive query with the count(*) function, just because I'm limiting the result-set for pagination. Is there another way to instruct MySQL to count the whole query, but bring back only the current pagination data?

I hope my question is clear... thanks

Possible dupe of this? http://stackoverflow.com/questions/818567/mysql-pagination-without-double-querying — barnyr, May 03 '13 at 10:10

score 1 · Answer 1 · answered May 03 '13 at 10:08

I don't know MySql but for many dbs, I think you'll find that the cost of running it twice isn't as high as you'd suspect - if you do it in such a way that the db's optimization engine sees the two queries as having a lot in common.

Running

select count(1) from (
  select some_fields, row_number over (order by field) as rownum
  from some_table
)

and then

select * from (
  select some_fields, row_number over (order by field) as rownum
  from some_table
)
where rownum between :startRow and :endRow
order by row_number

This also has the advantage of you being able to maintain the query in just one place with two different wrappers around it, 1 for paging and 1 for getting the total count.

Just as a side note, the best optimization you can do is make sure you send the exact same query to the db every time. In other words, if the user can change the sort or change what fields they can query on, bake it all into the same query. E.g:

select some_fields,
   case 
     when :sortField = 'ID' and :sortType = 'asc' 
       then row_number over (order by id)
     when :sortField = 'ID' and :sortType = 'desc' 
       then row_number over (order by id desc)
   end as rownum
from some_table
where (:searchType = 'name' 
  and last_name like :lastName and first_name like :firstName)
or  (:searchType = 'customerType' 
  and customer_type = :customer_type)

This is what I was thinking, do a count on your "expensive query" using a windowed SQL function. Then toss that into a suquery to just grab the top N records or records N+10 etc. — J.T., May 03 '13 at 11:54

score 0 · Answer 2 · answered May 03 '13 at 09:31

0

cfquery has a recordcount variable that might be useful. You can also use the startrow and maxrows attributes of cfoutput to control how many records get displayed. Finally, you can cache the query results in coldfusion so you don't have to run it against the database each time.

answered May 03 '13 at 09:31

Dan Bracuk

20,699
4
26
43

Hi Dan and thank you for the quick response First, I'm using cfc functions as services called by a flex client so cfoutput is not possible for this scenario. Second, if I use RecordCount of the cfquery object, it means I need to select in theory the actual data for 10000 records just to count it, and I still need to return just let's say 10 records out of it... so regarding cache, you mean chachWithin? I'm not sure about this one since each call to the server might be with different filters let's say for the house size or area typ etc... so I can't see how can I cache this kind of query...??? – Ajar May 03 '13 at 10:27
Are you returning a query object to the client, or are you transforming it? In either case, you can use the cf functionality in your cfc to transfer the correct amount of data. – Dan Bracuk May 03 '13 at 11:46

How to avoid running the same expensive MySql query twice in pagination?

2 Answers2