Home arrow PHP arrow Page 3 - Optimizing System Performance

Database Optimizations - PHP

In this second part of a two part series, you'll learn how to use debuggers and optimize performance. It is excerpted from chapter 12 of the book Zend PHP Certification, written by George Schlossnagle et al (Sams; ISBN: 0672327090).

  1. Optimizing System Performance
  2. Optimizing Performance
  3. Database Optimizations
  4. Keep Your Code Simple
  5. Exam Prep Questions
By: Sams Publishing
Rating: starstarstarstarstar / 8
November 30, 2006

print this article



Although we've briefly discussed databases in Chapter 9, "PHP and Databases," it's a good idea to start thinking about them in terms of performance. When you execute a database query, you depend on an external resource to perform an operation and, if that operation is slow, your entire website will suffer.

There is no predetermined "maximum number of queries" that you should use when writing database-driven websites. Generally speaking, the higher the number, the slower a page will be, but a single badly written query can slow down a web page more than 20 well-written ones. As a general guideline, most developers try to keep the number of queries performed in every page below five—however, many websites use a higher number without suffering any significant performance degradation.

Optimizing the tables that your queries use is the first step toward ensuring fast data access. This means that you will have to normalize your database so that a particular field is stored only in one table and each table is properly linked with the others through foreign keys. In addition, you will have to ensure that all your tables have been properly indexed to ensure that the queries you execute can take full advantage of the DBMS's capability to organize data in an efficient way.

Naturally, your optimizations should not come at the expense of security. Always make sure that you escape all user input properly (as discussed in Chapter 9) and that the statements you perform are safe even if the database itself changes.

For example, consider this simple query:

INSERT into my_table
values (10, 'Test')

This query expects that my_table will always have two fields. If you extend it to include additional columns, the query will fail. This might seem like a far-fetched scenario, but it really isn't. A complex application often includes hundreds, or even thousands, of queries, and it's easy to forget that one exists when making such sweeping changes.

On the other hand, it's easy enough to fix this problem by simply rewriting the query so that it specifies which fields it intends to insert data in:

INSERT into my_table (id, name)
values (10, 'Test')

In this case, it will be a lot more difficult for an error to crop up—but by no means impossible. If the new fields you have added to my_table do not accept null values and have no default values defined, the query will still fail because the database won't accept empty columns. Thus, you really have to be careful when making changes to your database!

>>> More PHP Articles          >>> More By Sams Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates


Dev Shed Tutorial Topics: