PHP and MySQL for Dynamic Web Sites: Visual Quickpro Guide, Second Edition

Database Optimization

As a conclusion to this chapter, I want to mention several performance-related techniques with respect to databases. The busier a site is, the more important each little tweak will become.

The performance of your database is primarily dependent upon its structure and indexes. When creating databases, try to

• Choose the best storage engine

• Use the smallest data type possible for each column

• Define columns as NOT NULL whenever possible

• Use integers as primary keys

• Judiciously define indexes, selecting the correct type and applying them to the right column or columns

• Limit indexes to a certain number of characters, if applicable

Along with these tips, there are two simple techniques for optimizing databases. I’ll discuss each next. ...