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 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.
If you do a simple
SELECT statement with a
WHERE clause, an index won't be used. There are ...