[Phpwebsite-developers] It's not the Classes, it's the Queries

[Eloi G. <el...@re...>]

Hey all.

I had the weirdest dream.  At one point I'm riding a bicycle (badly) through
the surf & this narrator kept repeating over and over something about every
time a query is received the mySQL server has to parse, optimise, build
tables, access, analyze & report data.

When I woke up, I trundled over to my system & inserted this line in
database.php @line 92:
echo $sql.'<br />';

What I saw next really troubled me.

My 0.91cvs testbed homepage queried the server 1,061 times!  I next called
up an Announce article
(http://www.realvidreams.com/testbed/index.php?module=announce&ANN_user_op=v
iew&ANN_id=42) and the query count dropped to a still hefty 462 times!

I could understand if the server was busy pulling up relevant data, but a
lot of the queries were duplicates!  Take this for instance:

select * from 4_modules where mod_title = 'calendar'

Did that really need to be done 63 times to create 1 homepage?  This was
almost rivalled by:

select * from 4_cache where mod_title = 'calendar' and id =
'4124aabad14bfcc1d0d0b8840b98b0b1'
which showed up 29 times!

MenuMan was another area of concern.  If we need to build a tree it would be
better to load all data into memory, assemble the relationships and
immediately unset() whatever data we don't need.  Especially if your SQL
server is on a different machine or (Oh God, No!) behind an ODBC driver.
That method would also cut down significantly on the 206 queries that were
generated.

The cache table was queried 717 times.  Of that, stuff was selected 120
times, inserted 149 times, & deleted 299 times.  My sleep-addled brain
didn't want to find out what happened on the remaining 149 queries.  Would
phpWS still work if it wrote to the cache -after- it finished working with
all the data?

Menuman & Calendar seem to be the biggest culprits, but I'm also seeing
duplicates of queries like these:
select * from 4_modules where mod_title = 'fatcat'
select * from 4_modules where mod_title = 'comments'
where just doing a global SELECT and storing the values in memory can reduce
the query count by 74.

All this leads me to believe that phpWS's performance problems are more due
to excessive database usage than classes.

-Eloi-