Improve Script Performance

Hi,

Recently I developed some webpage and I found that most of the part that slow down the execution of a php script is due to the mysql connection. When there are more mysql queries in the page, the whole page slow down a lot.

There was one web application that I developed having lots of query writing to the database which result in about 20 to 25 sec of execution. So, I reprogram it to make all result into an array and only store once into the database at the end of execution. This way the execution time decreased to less than 5 sec.

To bring a faster web application, I am thinking it's better to have mysql query (read or write) as minimal as possible. So I am thinking to have the query run once on the beginning of the script to get every data we needed for that whole execution and store into array. Have the rest of the query works with the array, filter the result from the array to get detail result. Anything that need to store to the database, will be stored in another array and will only write once to database on all of it at the end of the script.

Do you think it is a good idea for my above suggestion? Or is there any ready solid solution for this kind of general performance improvement issue?

Reducing the number of queries you run is an important optimization step, but it's not necessary to run all of your reads at the beginning and all of the writes at the end. The time at which you run the query has no effect on performance, only the number of queries that you run (and the queries themselves of course) affect performance.

One thing you should almost never have in your scripts is queries inside loops; that is a key indicator that you should be using a join or better conditions instead.