.When changing the number of rows per page, the grid does not jump back to show a page having some rows, but becomes blank as no rows fit in the page.
Example:
With records = 143.
nPage = 1, nPerPage = 50, shows rows 1-50 (correct)
nPage = 3, nPerPage = 50, shows rows 101-143 (correct)
nPage = 3, nPerPage = 100, shows no rows (technically correct, usably incorrect)
What should have happened, IMHO:
1. When the data fetched shows that there are no rows for the requested nPage and nPerPage
, AND
2. records shows that there are rows that can be fit into a previous page
, the component should requery with either:
A) The lowest page number that ALL the records can fit into
, OR
B) The highest (next) page that can hold at least one row
You're doing a stellar job with this component, btw, and I'll nag my employer for a contribution to your continous efforts, and try and contribute with a Coldfusion custom-tag.
Yes, your solution fetches all rows to the application-server, then paginates them there before sending the records to the client.
I'm talking about fetching only the n records from page m, from the database-server, as quickly from page 1 as from page 10 million.
I'll explain better what I mean later. 
EDIT: I had a look at it, and although MySQL has a LIMIT-clause, there is nothing which indicates to me that it doesn't scan the rows.
As described here: http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/
“Also note – number of records scanned to provide result set will be very dynamic based on particular constant and other factors.
For example for our dating example if we use only (last_online) index and look for people from USA we likely will find 10 people pretty quickly, if the country is small or simply there are few members from the country, ie Slovenia – same kind of search might need to scan 1000s times more rows to provide result set.”
My work-around is to create a table holding the order-fields-for-first-row-in-each-page, and use that together with indexes.
Indexes do not alone provide an optimal solution in the case of pagination.
No it does not, but please see my edited post.
I'll provide a working example soon as I've finalized it.
EDIT: What I am saying is that whilst you are getting i.e. 10 records back, the SQL engine has to actually consider rows * page records in order to provide you with these 10 records.
Unless you say “give me 10 records back fullfilling my order by criteria, starting from a row which fullfills the criteria for the first row in the page that I have found and stored to help optimize this query”.
Also when I say “table” I mean a recordset returned to the application-server, which it can use to query the database, if the filter and (multicolumn) order hasn't changed.
Copyright 2014 TriRand LtdAll Rights ReservedRSS
Back to Top
