[thelist] SQL optimization

[Andrew Forsberg]

Hello All!

After Dan's recent encouragement to ASK ASK ASK I thought I would!

I have an SQL optimization question that goes like this: on a catalog 
page I currently have two SQL queries. One takes the search criteria 
and selects a count of the code column (which is the only one 
guaranteed to be unique) -- this result I use to work out the number 
of catalog pages for navigation purposes. The count is the only thing 
I'm selecting in this query. The second query uses the same criteria 
plus a sort and limit clause, and it selects all the info which the 
catalog page will display (between 1 and 6 columns).

Now, is this an incredibly inefficient way of doing things? I thought 
that using MySQL to limit the entries returned would be the good 
thing to do... I'm sure MySQL would do the sorting and limiting 
faster than PHP (which is the scripting language I'm using here), and 
the first query is only counting matching records. Still, could 
someone with more experience with databases point me in the right 
direction here? I'm a bit worried that two SQL queries a page is a 
bit of a strain on the server (which will not be a dedicated 
production server).

Thanks heaps all you DBM gurus.

Andrew
-- 
Andrew Forsberg
http://www.thepander.co.nz