PHP/MySQL SELECT Benchmarking

Jani Reinikainen,

21. March 2003

Introduction

Testing environment: Intel Pentium III (Katmai) 600MHz@672MHz, 256mb SDRAM, 80gb Western Digital 5400rpm IDE hard drive, Debian GNU/Linux 3.0r1 aka. Woody/Stable, PHP 4.1.2, MySQL 3.23.49, Apache 1.3.26.

The test was run with actual snippets of applications currently in use by undisclosured communities. The test scripts used to use a simple database abstraction layer for PHP, which was written by me. In these new verions, I have changed over to PEAR's database abstraction layer, as maintaining my own was too much work (and a bit like re-inventing the wheel). The benchmark itself is a part of PEAR. Incase you're wondering about the LATS.php, it's just a collection of text and string related functons, written by me.

Benchmarking

Method 1: Limit number of retrieved rows in SQL statament.
- Advantages:
  - Lower strain on database, SELECT is LIMITed.
- Disadvantages:
  - SQL arguments need to be passed to each page, so that the script knows where we left off.
- Benchmark:
  - sqlbench1.phps
  - Result: Using 250 rows of inserted data, execution time was on average 0.028655 seconds.
Method 2: Read all rows to a PHP array and manipulate the array.
- Advantages:
  - Err... it works?
- Disadvantages:
  - Each time a new page is loaded, a new "SELECT ALL" SQL query is made -> more strain on database.
  - SQL arguments need to be passed to each page, so that the script knows where we left off.
  - Huge array with irrelevant data, which is not displayed to the user.
- Benchmark:
  - sqlbench2.phps
  - Result: Using 250 rows of inserted data, execution time was on average 0.070571 seconds.

Conclusion

Method one is clearly faster (not to mention memory usage), even on small databases like my testing environment. With larger databases, the difference in speed is even more noticeable, in favour to method one.

References

sqlfoobar.phps: a PHP script I wrote, that inserts random data into a SQL table, for testing purposes.
MySQL
PHP
PEAR