Indexes are used for reading the data from the table with comparatively faster execution time. When an index is defined, MySQL maintain separate list of index values and update them when the values are updated / changed. Defining index will require more storage that means we are trading off space with the speed.

MULTI-COLUMN AND SINGLE COLUMN INDEX

Lets consider this table and its index:

create table employees (
id int auto_increment,
first_name varchar(32) not null,
last_name varchar(32) not null,
phone_number varchar(32) not null,
primary key(id)
);

ALTER TABLE employees ADD INDEX first_last_name (first_name, last_name);

mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|   100001 |
+----------+
1 row in set (0.01 sec)

Index Cards' "DNA" By ayalan

Now lets make selection:

SELECT * FROM employees where last_name='tuladhar' and first_name = 'prajwal';

mysql> explain select * from employees where last_name='tuladhar' and first_name='prajwal' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: ref
possible_keys: first_last_name
key: first_last_name
key_len: 196
ref: const,const
rows: 1
Extra: Using where
1 row in set (0.00 sec)

By using EXPLAIN keyword we can know which keys are being used in the query. (Focus on possible_keys and key column)

This is simple example of using multi-column index.

Now, instead of using, double criteria, lets use only single.

SELECT * FROM employees where last_name='tuladhar';

mysql> explain select * from employees where last_name='tuladhar' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employees
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 100001
Extra: Using where
1 row in set (0.00 sec)

Did you see that the key column is null. So, the query is not taking advantage of any index at all. This is because we have only defined two indexes: a primary key and multi-column index containing first_name and last_name.

Now lets use ORDER BY clause to sort data using columns: first_name and last_name

SELECT * FROM employees order by last_name, first_name LIMIT 10;

mysql> EXPLAIN SELECT * FROM employees order by last_name, first_name LIMIT 10 \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: employees

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 100001

Extra: Using filesort

1 row in set (0.00 sec)

If the sort order has last_name as the first column while first_name as the last, index won’t be used. In order to make the query use the index we have to :

SELECT * FROM employees order by first_name, last_name LIMIT 10;

mysql> EXPLAIN SELECT * FROM employees order by first_name, last_name LIMIT 10 \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: employees

type: index

possible_keys: NULL

key: first_last_name

key_len: 196

ref: NULL

rows: 10

Extra:

1 row in set (0.00 sec)

Now lets create two additional single column index:

ALTER TABLE employees ADD INDEX first_name (first_name);

ALTER TABLE employees ADD INDEX last_name (last_name);

SELECT * FROM employees where last_name='tuladhar' and first_name = 'prajwal' ;

mysql> EXPLAIN SELECT * FROM employees where last_name='tuladhar' and first_name= 'prajwal' \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: employees

type: ref

possible_keys: first_last_name,first_name,last_name

key: first_last_name

key_len: 196

ref: const,const

rows: 1

Extra: Using where

1 row in set (0.00 sec)

In the above query, there are 3 possible queries and MySQL is using the multi-column index. We can force MySQL to use other two indexes by giving it hint.

SELECT * FROM employees IGNORE INDEX(first_last_name) where last_name='tuladhar' and first_name = 'prajwal' ;

mysql> EXPLAIN SELECT * FROM employees IGNORE INDEX(first_last_name) where last_name='tuladhar' and first_name = 'prajwal' \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: employees

type: ref

possible_keys: first_name,last_name

key: first_name

key_len: 98

ref: const

rows: 1

Extra: Using where

1 row in set (0.01 sec)

The above query is using two single indexes instead of a multi-column index.

Consider the query:

mysql> EXPLAIN SELECT * FROM employees where last_name='tuladhar' or first_name = 'prajwal' \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: employees

type: index_merge

possible_keys: first_last_name,first_name,last_name

key: last_name,first_last_name

key_len: 98,98

ref: NULL

rows: 2

Extra: Using sort_union(last_name,first_last_name); Using where

1 row in set (0.00 sec)

It is using single column indexes instead of multi-column. That means, Multi column indexes are helpful when you need AND criterial and single column indexes are helpful when you need OR criteria.

PRIMARY KEY AND UNIQUE KEY

Primary Key can’t be NULL while Unique key can be NULL but that column can have only one value of NULL. (a bit weird right? This is because NULL is also a valid data format in MySQL )

FULL TEXT INDEX

I also discussed about this topic in my last post.

When executing a query with varchar and text type and if the column has large number of values, it is generally recommend to use Full Text Index rather than simple pattern matching using LIKE and/or Regular Expression.

AVOID USING SQL EXPRESSIONS WHEN POSSIBLE

This is because SQL expressions can’t take the advantage of indexes. I have taken example from this post:

SELECT * FROM customer WHERE year(customer_join_date) = 2009;

SELECT * FROM customer WHERE customer_join_date BETWEEN '2009-01-01' AND '2009-12-31';

First query can’t take advantage of index while second query can only if `customer_join_date` is defined as index.

References:

• Multi Column indexes vs Index Merge
• 3 ways MySQL uses indexes

Hope this helps you. I would love to hear your feedbacks