There are 2 main focuses I will take to analyze a database. First, I will find out how it manages the data. Second, I will look at how it scales in term of data volume and traffics. Today, I will talk about the most common indexing scheme that most of the databases use today. It is B-Tree Indexing.
B-Tree Indexing

When should you use B-Tree Index?
To understand when you should use B-Tree index, you should know there are 2 ways to use an index. First, you can use index as a mean to access rows in a table via rowid. If you use index for that, you want to access a very small percentage of the rows in the table. Otherwise, you need to get into "index then row" cycle many times (implies many IOs) and it will be worse than pulling bunch of rows in batch to reduce the number of IOs (the costly part of database operation). According to the experiment done, full scan is faster if we access too high % of rows via index. Second, you can use index as a mean to answer the query if the index contains enough information to answer the entire query. In this case, we don’t need to go to the table at all. The index will be used as a thinner version of the table. So, if you want to access a large % of rows via index, you should consider to get the query answer via the information in the index.
MySQL Indexing
There are several rules to remember for MySQL indexing
- MySQL will only ever use one index per table per query (except for UNION b/c it is considered as separated queries).
- To get around that, you can create multicolumn indexes.
- When there are more than 1 indexes to choose from, MySQL makes an educated guess based on the statistics gathered.
- MyISAM has indexes kept in a completely separate file from table rows. And table rows are stored in the random order that are retrieved by the rowid in the index items.
- InnoDB uses clustered indexes that has primary key and the record itself clustered and the records are all stored in primary-key order. When your data is almost always searched on via its PK, clustered indexes can make lookups incredibly fast because single lookup can pull out record in question.
- Primary key cannot contain NULL whereas unique index can.


















