performance of the database and php

searain

I need good advice and resources, reference books, web sites etc. regarding how to write good program with the best performance in php's database executions.

I am using InterBase in most cases, and MySQL in some cases.

Such as the usage of complicated SQL statments, union, joint, subqueries or use the php programming to divide the complicated SQL to small SQLs, in which cases, which approach is better?

When and how to close the connection to release the server resources etc.?

Or in what case, I should care about clean programming rather than small performance improvement due to the hardware's improvement will improve so quick that I don't have to worry about the performance too much (unless a few very bad performance programming. such as ...)

Thanks!

denvervender

There are different ways to improve the performance of your system. It depends upon your hardware, programming practice, number of transactions etc. There are also general concepts and solutions to better system performance of your system.

I don't know if this may help you.
These are the most common practices that I do in tuning my system. 🙂

Check your datatypes
Create Indexes
Check your queries. (I always use the EXPLAIN to check the execution time of my queries. Some queries are faster without index)
Tune your configurations (Database and Web Server).
Check your memory usage, avoid memory swapping.
I check the execution time of my php scripts by using "time" and "lynx" or "curl"
I use connect rather than pconnect
VACUUM your database
APC (Alternative PHP Cache)

You might want to check out these links.

http://www.mysql.com/information/presentations/presentation-oscon2000-20000719/index.html

http://www.linux-mag.com/2001-06/mysql_01.html

http://www.omniti.com/~george/talks/

Danicek

You can also look for documentation for some expensive db systems such as Oracle or Sybase. You will find in their documentations whole books about tuning and performance.
And big parts of these books are about general things that could be used in every db such as
- how to use indexes
- how to partition tables
- how to use multiprocessing
- how to desing your queries
....