Posted by Saul on 12/06/06 15:41

Vincent Delporte wrote:
> A friend is running a community application in PHP on a shared
> server, and his provider is complaining that it's using the DB too
> much.
>
> Apart from making changes to how data are organized in the DBMS, are
> there ways in PHP to minimize DB load, eg. caching data instead of
> reading/writing them from/to the DBMS every time, etc.

If you can get some timings on the queries and a count of how many
queries are running per page. If he's abstacted the query to a separate
class this is quite simple, just capture the beforetime and an
aftertime around a query.

Some very obvious things slow down queries. Using select * from table
is a lot slower than selecting explicit fields from a table,
particularly if there are lots of joined tables. Left outer joins are
typically very slow.

There are a lot of instances where one long very heavily joined select
statement would be better off as two or more shorter selects

for instance to find the most recent item added

select id from headtable order by whenAdded DESC limit 0,1
select * from itemtable where headid=$id

is likely to be quicker than the bigger

select * from headtable inner join
itemtable on headtable.id=itemtable.headid
order by headtable.whenAdded DESC

However, the converse is also true, firing off the same query
repeatedly in a loop is actually a slow and intensive way of working -
better to have the query pull lots of answers and iterate through them.

Outside these, until you look at the timings and quantities and types
of queries generated it's tough to know other places to look. As an
example our website is completely (absolutely completely) database
generated and generates a page with between 10 and 25 query statements
(it depends on what the user wants shown on their site/pages) of a
database which has 70+ different database tables. We're continually
trying to tune it to reduce the number of queries and optimise the
speed of those queries.

If you can't reduce/improve the database queries then it may be that
you've outgrown the existing database/server infrastructure.


Saul
www.notanant.com
communities of websites

[Back to original message]