Re: first-prev-next-last using PHP

From: Matty (matt+nntp_at_askmenoquestions.co.uk)
Date: 12/13/03 

Date: Fri, 12 Dec 2003 23:09:09 +0000

Adi Schwarz wrote:

>> select *
>> from mytable
>> order by some_field
>> limit zero_based_offset, record_count;
>
> Of course that works, but the point is that this query is executed once
> for every page of the result set -> the server gets all rows, sorts them
> and then takes the rows he needs (normally the minority of all rows) -
> for every single page, always (almost) the same query. I would say it
> saves database server load if this is only done once.
>

Depends on how your code is written, whether you use persistent
connections, etc. Bear in mind, that if the user is only likely to want
to see 10 records, then it's maybe a little wasteful to fetch 2000 that
they won't ever see.

If you're talking about caching the actual data returned, then look at
using an application-level cache. I personally go the roll-your-own
route, but PEAR has a Pear::Cache class (or similar) that does this.

And just how large would the entire result be? How much do you want to pull
across the network connection to the DB server, how much do you want to
serialize/deserialize to/from disk?

Better still, why not just look at caching the page output (depending on
your application), saving executing most of your php and database code at
all?

Relevant Pages

  • Re: limiting table access and RWOP queries
    ... your code is not executing a stored query. ... Queries that are executed through code, ... > If I log in as the owner of the table, ...
    (microsoft.public.access.security)
  • RE: Output Query Problem
    ... What is the total record count of the resulting Query? ... having a problem with my VBA code executing when the DB is on the server it ... does not output my query to create an excel file on the server. ... I would get the msgbox "no Data, no report" (an ...
    (microsoft.public.access.modulesdaovba)
  • Re: long running select min(timestamp) query
    ... the query seems to be taking upwards of 10 minutes!!! ... Execution Plan ... You can see if the migrated rows are fixed by ... truncating the CHAINED_ROWS table and executing the same analyze table ...
    (comp.databases.oracle.server)
  • Re: Pre-allocate large amount of memory?
    ... in the query to ensure the query can be built in a reasonable way. ... In PostgreSQL and Microsoft SQL Server you can use WITH expression: ... Only if that also fails _then_ I would switch to a caching ... The reason: I would try to keep architecture simple and avoid ...
    (comp.lang.ruby)
  • Re: Invalidate cache
    ... I run my queries on a machine that is doing nothing except executing my ... Before every query I execute ... SET TRACE POINT DM421 ... The host machine is only executing VMware. ...
    (comp.databases.ingres)
Loading