[thelist] SQL optimization

[rudy]

> Now, is this an incredibly inefficient way of doing things?

hi andrew

off the top, i'd say no, not at all

to recap, you are doing one

    select count(*)

in order to display "records 1 to 10 of [count]" followed by

   select stuff
     LIMIT x , y

where you may or may not use the total count to decide the values of x or
y -- most sites just do it in chunks of ten, so you see pages saying
"records 1 to 10 of 11, click here to see next 10"

as far as efficiency is concerned, i think the only alternative to limiting
the rows on each query in this way is to get them all at once and hold them
in the web server, thereby making second and subsequent page requests
faster because they don't go back to the database

clearly for some queries you just can't do that, as the number of possible
records might be prohibitively large

on the other hand we had a thread similar to this recently about storing
state codes in application variables -- i think we were using the terms
session and application as cold fusion does, session variables for
individual users and application variables for all users

(the application variables proponents never did answer my question about
why they are coding something that i thought cold fusion did using query
caching -- okay, i'm being petulant)

> I'm a bit worried that two SQL queries a page is
> a bit of a strain on the server

no, your first query gets the total count while your second gets rows,
that's about as simple as it gets

assuming you don't always get the count, just the first time   ;o)

there *is* a way to combine these into one request, but it's ugly (it uses
a UNION and the database has to process both queries anyway)


also, keep in mind that if you are showing something like search results,
hardly anybody keeps hitting the "next 10" link to get all the way to the
end -- in a case like that, chunking the results makes sense, it would be
wasted processing if you did get all database records at once and loop
through them on the web server

hope this helps


rudy