Category — Indexing

Slides of my talk on B+Tree Indexes and InnoDB

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.

December 18, 2011   Views: 7,727   1 Comment

Understanding B+tree Indexes and how they Impact Performance

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.

July 14, 2011   Views: 36,190   7 Comments

On Covering Indexes and Their Impact on Performance

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.

July 4, 2011   Views: 9,943   2 Comments

A few notes on InnoDB PRIMARY KEY

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.

April 24, 2011   Views: 17,328   26 Comments

Understanding InnoDB clustered indexes

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!

January 20, 2011   Views: 29,569   23 Comments

MySQL Indexes – Multi-column indexes and order of columns

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. How does the order of column in the index matter? The order of columns in the index matters a lot,…

May 26, 2010   Views: 25,367   19 Comments