[thelist] php/mysql speed

[Hans Zaunere]

--- John Corry <webshot at members.evolt.org> wrote:
> I was working on an architecture for a site we're building and came
> up with
> a question:
>
> My site will have its' nav generated per page based on the contents
> of a
> MySQL db. The nav will be built from elements retrieved from a query
> something like:
> $nav_query = SELECT prodID, prodName, prodCategory, prodAvail from
> products
> . 'products' is a table with about 100 items in it. So...I know the
> easy way
> to do it...just run the query and build the nav on every page. But is
> that
> the best way?

The "best" way?  If you get 100000 hits/second, then probably not.  If
you get 1000 a day then it probably is.  I would go with hitting the
DB.  It's clean, easy, and maintainable; then, if you really see a
performance problem, think of something else.

> It seems like a lot of unnecessary queries to the db.

Yeah, it probably is.  Unless you're Amazon.com though, it's probably
not a big deal.  MySQL is built for this kind of thing, and if you're
really worried about it, use temp (heap) tables and tweak MySQL for
many SELECTs to a particular table.  You'd be amazed what modern
processors can do these days.

>
> I don't have a lot of experience with sessions, but I was thinking
> maybe I
> could do something like:
> session_start();
> if (!$nav){
> session_register('nav');
> $nav = mysql_query($query);
> That way, I have the result set that I need to build the nav
> available on
> every page, right?

This is a horrible idea.  To start, you're going to leave it up to the
client to maintain a nav bar?  Drop this.

> My other idea was to let the product update script (where prodName,
> prodCategory, etc get updated) build a static navigation file and
> then just
> include that file. I don't know what's slower, a query to the db or a
> disk access to include a file.

A DB query will always be slower, especially if it's out across a
network.

>
> Is there some commonly accepted 'best practice' for the kind of
> functionality I'm working on?

There is;  it's called "what works."  Start out simple and easy; if
performance gets to be a problem, (assuming you can design code
modularly) drop something else in (caching a static nav file would be
my next step).

Hans Z.
New York PHP
http://nyphp.org



__________________________________________________
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/