B-Tree index in mysql

Posted on | Edited on | In

Create a table like this:

1
2
3
4
5
6
7
CREATE TABLE People (
   last_name  varchar(50)    not null,
   first_name varchar(50)    not null,
   birthday   date           not null,
   gender     enum('m', 'f') not null,
   key(last_name, first_name, dob)
);

Create a b-tree index by two name and birthday.

B-Tree structure’s data is Sequential

Use B-Tree index

First of all query’s sequential is required
last_name -> first_name -> birthday
if miss a key then the later condition is invalid.

  1. Match the full value to all fields specific val.
  2. Match a leftmost prefix search the the man who’s last_name is ‘Leon’
  3. Match a column prefix last_name like ‘L%’
    ps. the follow condition will be useless
  4. Match a range of values
    date_format(birthday, ‘%Y-%m-%d’) > ‘2017-04-29’ and
    date_format(birthday, ‘%Y-%m-%d’) < ‘2017-05-01’
  5. Match one part exactly and match a range on another part
    last_name=’Leon’ and first_name like ‘L%’

Rule

  1. Sql condition in B-Tree index must use the first field in index first.
  2. Sequential search condition, jump is useless(follower)
  3. Put range search condtion to the end of where
    the follow index field is invalid