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.
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.
Copyright 2014 TriRand LtdAll Rights ReservedRSS
Back to Top