MySQL index performance and example

The aims for index in MySQL is to find row quickly. For example, there is a table like


CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),   
);

If we execute
select * from test where last_name = 'Obama';

the MySQL will check all records in table test. It will be the O(n) questions.

If we use index for this table, it is not necessary to check each row from 0 to n.  How to improve the performance to search one or more special rows in table?

For example, when we want to find a keyword in a book, we find it in the index list and then get the page no from it. We can find it quickly based on the book index.  MySQL may also use this method to get it by index.

Now we use command

alter table test add index(last_name);

We have added one index on table test for last_name. if we execute
select * from test where last_name = 'Obama';

MySQL will find it from its index files, not from record 1 to last one. Of course, we should use more disk space to store the index files.

There are three ways to create index


Index for one column

alter table test add index(last_name);


Parts index for one column

alter table test add index(last_name(4));

it will save space.

Index for multiple columns

alter table test add index(last_name, first_name);

In fact, the MySQL will one execute one index for last_name or first_name. MySQL will determine which one is used dynamically. E.g. If first_name is less, first_name will be used.

invalid command
 SELECT * FROM test WHERE first_name='Michael';
SELECT * FROM test WHERE last_name='Widenius' OR first_name='Michael';

valid command


SELECT * FROM test WHERE last_name='Widenius';
SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael';
SELECT * FROM test WHERE last_name='Widenius' AND (first_name='Michael' OR first_name='Monty');
SELECT * FROM test WHERE last_name='Widenius' AND first_name >='M' AND first_name < 'N';



Unique Index for one column

alter table test add unique (last_name);

It will check the unique attribute before insert a new record

Data structure
Index will use B-Tree to create a index file.

Get all index columns
show indexes from test;

When will you should use index
1. find a fixed string, e.g. where last_name='obama';
2. join two table, e.g. on table_a.last_name =b_table.last_name
3. find a range e.g. where id > 100
4. Min(), Max()
5. order by , group by