On Tue, Dec 04, 2001 at 08:38:37AM +0100, [EMAIL PROTECTED] wrote: > Hi all, > > see below > > > That's not going to scale well to large tables/results. > > I'd go with caching the rowid's from the query somewhere (db or file). > > > > Tim. > > > > On Fri, Nov 30, 2001 at 10:52:34AM -0800, Brent Cowgill wrote: > > > I solved this problem just the other day after searching google. I > > > needed to have an order by and where clause preserved. Here's what I > > > came up with. Note, I haven't done any stress testing on this query > > > yet. If anyone comes up with an improvement I'd be interested in > > > getting a CC. > > > > > > SELECT > > > fields > > > FROM > > > table > > > WHERE > > > rowid IN ( > > > SELECT > > > rowid > > > FROM > > > (SELECT rowid > > > FROM table > > > WHERE criteria... > > > ORDER BY order... > > > WHERE rownum <= last_row_num_wanted > > > MINUS > > > SELECT > > > rowid > > > FROM > > > (SELECT rowid > > > FROM table > > > WHERE criteria... > > > ORDER BY order...) > > > WHERE rownum < first_row_num_wanted > > > ) > > > ORDER BY order... > > What about a combined approach: Cache the first, say 20 results pages > as static temp html files and re-perform the query from cached row > ids only if the user clicks a "more..." link. This way it should be > possible to avoid repeated queries based on identical search > parameters amd make the whole thing fast..
The inner order by clauses force the db to get and sort _all* matching rows each time the query is run, regardkess of how many result rows are generated. Tim.
