The slides of my talk on B+Tree Indexes and InnoDB are now available for download. This slide was presented during Percona Live London 2011. You can download the slides from here. There are many other interesting and informative talks that were presented during Percona Live London 2011, and I think you should definitely check them out, if you haven't. They are available here.
Indexes are a very important part of databases and are used frequently to speed up access to particular data item or items. So before working with indexes, it is important to understand how indexes work behind the scene and what is the data structure that is used to store these indexes, because unless you understand the inner working of an index, you will never be able to fully harness its power.
The purpose of this post is to describe what covering indexes are and how they can be used to improve the performance of queries. People mostly use indexes to filter or sort the results but not much thought is given to actually reduce the disk reads by using proper indexes. So I will show you how to reduce disk reads and hence improve the performance of queries by utilizing indexes properly.
InnoDB uses an index-organized data storage technique, wherein the primary key acts as the clustered index and this clustered index holds the data. Its for this reason that understanding the basics of InnoDB primary key is very important, and hence the need for these notes.
Some people don't probably know, but there is a difference between how indexes work in MyISAM and how they work in InnoDB, particularly when talking from the point of view of performance enhancement. Now since, InnoDB is starting to be widely used, it is important we understand how indexing works in InnoDB. Hence, the reason for this post!
The problem: Many a times people find that they create index but the query is still slow or the index is not being used by MySQL to fetch the result-set. Mostly the reason is that the index is not created properly, either not the right columns being indexed or the order of columns in the index does not match how its being used in the query. The order of index! What’s that. Well that’s what we will be discussing today. …