O'Reilly logo

Learning PHP and MySQL by Jon A. Phillips, Michele E. Davis

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Advanced SQL

In this section, we'll introduce database concepts that, while not strictly necessary for developing your web sites, can increase performance and give your queries more flexibility.

Indexes

Indexes work the same way that an index of a book works. If you were to look for the keyword "create table" without an index, you'd need to spend a lot of time scanning through the pages of the book looking for a section that might be relevant. Then you'd have to scan the entire section. This certainly isn't an efficient use of your time or the database's. The solution is an index at the end.

The data in an index is sorted in order and organized to make finding a specific value as quickly as possible. Because the values are sorted, if you're looking for something specific, the database can stop looking when it finds a value larger than the item you're looking for.

You face the same problems as a book does, though. If an index is so great, why not index everything? There are numerous reasons:

  • There's only a finite amount of space available.

  • When writing books, it becomes inefficient to generate and maintain a gigantic, all-encompassing index.

So some intelligent decisions about which fields to index in your tables have to be made. Each index requires its own datafile for storage, which can add a bit of processing time when the contents of an indexed field changes in the database.

When indexes are used

If you do a simple SELECT statement with a WHERE clause, an index won't be used. There are ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required