Database Optimizations - PHP

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!