SteveHinesley.com

My Personal Technical Resource

Speed up output of MySQL driven PHP scripts

If you are pulling a fair amount of data into your PHP script from a MySQL database and dumping the information to the screen and there is a noticeable delay when rendering the web page, then you might want to give this a try.

The first thing that we want to do is to disable output buffering in PHP. Disabling output buffering will immediately output to the web browser. Normally PHP will buffer the output so that if there is an error, it is displayed and the script is terminated, without outputting anything else. Sometimes this is annoying when trying to track something down, so you can also use it to help troubleshoot problems as you can get a better sense of where the problem lies.

Add the following code near or at the top of your PHP source:

ini_set('output_buffering', 0);

That should make a noticeable improvement in the speed that the page is sent to the web browser.

Now, there is a function that can also help speed up things on the MySQL side, mysql_unbuffered_query(). You can simply use it as a drop in replacement for mysql_query(). When you use mysql_query(), you have to wait until the whole query has finished before it will return any data. With mysql_unbuffered_query() you start receiving data immediately, but there are some differences, such as you cannot use mysql_num_rows() or mysql_data_seek(). So you need to structure your code accordingly.

Replace any occurance of mysql_query() with mysql_unbuffered_query() when you are just dumping most of the query results.

...
//   $result = mysql_query("SELECT * FROM TABLE);
$result = mysql_unbuffered_query("SELECT * FROM TABLE);</p>
while ($row = mysql_fetch_row($result)) {
...

When using mysql_unbuffered_query(), if you structure your code so that you output the data immediately after each row is read in, it some cases I have seen it improve the speed so much that it can appear to be a static HTML page.

Speed up output of MySQL driven PHP scripts

Download Speed up output of MySQL driven PHP scripts as PDF

Tags: , , , , , , , , ,