Wow, subselects for getting the middle rows. Interesting. It'd probably be worthwhile to create a temporary table with the first field being an auto-increment. Then you could just do an join and restrict on the value in the auto-increment number.
But what is the cost of doing that for a site that gets 1000's of concurrent page views? The query optimizer likely will determine that it can keep the temporary rowset(s) in RAM.
You are right that it it works best for tables that already have an sequenced primary key.
For one server, to optimize it, we created a stored procedure that created the SQL on the fly from the input parameters (the where clause bits.) This was for a search engine. Then we saved the SQL in a cache table with fields matching the input parameters. We also saved the last n pages in a MRU row cache. That way, if the same request came in for the same page in the search results, and this was still in the cache, we just returned that without looking up the results in the master table. If the user wanted page 2, we already had processed the necessay SQL to generate it and then we cached that rowset as well after returning it.
You can also explore the ROWID method. I don't remember the exact syntax, but I've used it on Oracle to do the same thing.
The issues and ways to resolve them depend on the use cases. For a web search page, the most frequent page queries are going to be the first 1, 2 or 3 pages in decreasing frequency. So you don't always want to optimize for the any N'th page, when indexing and the first page will be enough.
Ed
-- Ed Howland WDT Solutions, LLC. [EMAIL PROTECTED] (314) 962-0766
_______________________________________________
CWE-LUG mailing list
http://www.cwelug.org/ [email protected]
http://lists.firepipe.net/listinfo/cwe-lug
