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 engine's. The solution is an index.
The data in an index is sorted and organized to make finding a specific value as quick 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 for which you're looking.
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.
• Too much data in the index means it takes longer to read the index when selecting data.
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.
Was this article helpful?