MySQL B-tree Indexes

B-tree indexes uses B-tree data structure, Most of the MySQL storage engine support this. MyISAM use prefix compression make index smaller , InnoDB leave values uncompressed ,MyISAM index refer to their index row by their physical value, InnoDB refers them by their primary key values

B-Trees store the indexed columns in order. They are useful to search ranges of data and also ORDER BY queries because indexed are in sorted order. B-Tree indexes work well, full key value , key range and key prefix. They are useful only if the lookup uses leftmost prefix of the index

create table devices (
     name varchar(50) not null ,
     description varchar(255) not null,
     device_imi varchar(255) not null ,
     added_date date not null ,
     key (name, device_imi, added_date)
)

It useful for following queries

• Match full value: Match with name of device thermostat , device_imi 125l7555, added_date 2011-05-06 like this
• Match leftmost prefix value: Match with name it use only the first column
• Match column prefix: Match first part of the column value get all devices begin with “th”
• Match range of values: Like between query but it also use only the first column
• Match one part exactly and range of another part: Like name exact match as “humidity sensor” and then device_imi range query
• Index only queries: Which access only index only not the row storage its call cover index

Limitation of B-Tree indexes

• If not start from the left most side of the indexed columns it will not useful: You can’t find with device_imi or date_added because these are not left most column and also you can’t find name with end of the strings
• You can’t skip columns on the index: Can’t find devices name and date_added , MySQL can only with name column only
• Storage engine can’t optimize access with any column to the right of the range conditions: For example WHERE name = “sensor” AND LIKE=”12%” AND added_date=2016-06-22 MySQL can use only first two column

Column order is extremely important. For optimal performance need to create indexed with same column order for different queries