Databases are just large data stores - they contain large streams of information, kind of like this book. Now, if you had this book in front of you and did not have the table of contents or the index at the back, how would you find a topic? You'd need to start at page one, and flick through every page in the book to find what you wanted, at which point you would stop and read. This is precisely what databases do when you query them - they search sequentially through all the data in a table, which can be very slow if you have large tables or if your data is near the end of the table.
To speed searches up, you can define your own custom indexes that provide information on where MySQL should look to find certain information, just like the index in this book. When you define an index, MySQL searches that first (which takes much less time than searching a full table), and when it finds a match it will jump directly to the correct place in the table. You can index as many fields as you want, and they can be of any type.
As you can imagine, indexes are incredibly useful tools to maximise the performance of your tables. However, you must be careful - not everything is rosy with indexes. For example, each index takes up space on your system, which means if you index every field "for maximum performance", MySQL ends up having to write twice as much information. Secondly, every time you update information in your table, MySQL needs to rewrite the indexes, which slows down changing data and adding data.
Author's Note: Adding an index to a field you regularly search can reduce a query from taking ten seconds to less than one-tenth of a second.
Want to learn PHP 7?
Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!
If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!
Next chapter: Persistent connections >>
Previous chapter: Referential integrity
Jump to:
Home: Table of Contents
Copyright ©2015 Paul Hudson. Follow me: @twostraws.