MySQL - Indexing

WHY?

Fetching results from a database is one of the frequent and time-consuming operations in any application. Since most part of your application is dependent on data and to fetch this data you need to communicate to your database multiple times, you would want this process to be as quick as possible and indexing helps very much in this task.

WHEN?

WHEN to use indexing? has a very short answer – “If your table contains too many rows.”

Small tables do not really benefit from indexing but large tables do since large table takes time to filter out data from a large number of rows. For example, if a table with a million records has to filter a row and in the worst case if the result row is the last row of the table then MySQL has to go through a million rows to fetch the data and it will be time-consuming.

HOW INDEXING WORKS?

When we index any column(s) it means MySQL will store the column(s) explicitly and whenever there will be any request to filter data on those column(s) MySQL will be using that stored index to filter data instead of doing a full table scan. This is how indexing makes query execution faster.

CREATING INDEX

Creating a new index on a column should depend on the queries which will be asking data from tables. In simple words whenever you create an index you should consider the query which will be used for the table and vice-versa.

While indexing follow these steps if you have no clue about which column to index –

1. Identify the where clause of queries used on the table.

2. If the where clause is using a single condition( single column) then create an index on that column. If the where clause is using multiple conditions there are things to keep in mind while creating a multi-column index.

MULTI COLUMN (COMPOSITE) INDEX

If a query is using multiple columns to filter result (WHERE clause on multiple columns) and there is an index on any one column we generally assume that the query will be using that index, but if you will check your query using EXPLAIN (details in next section), most probably you will find out your query is not using any index and instead doing a full table scan.

Syntactically adding a multi-column index is easy but there are certain things which should be kept in mind while creating multi-column index –

1. Ordering of columns – The ordering of column mentioned in the where clause of the query should be the same as the order of columns in the index.

For example –

SELECT id FROM table WHERE uid = 10 and status = 1
Here, index on (uid, status) and index on (status, uid) both will work differently even though both are using the same columns for indexing. For the above query INDEX(uid, status) will be picked by MySQL as both query and index are having the same ordering sequence. So while creating a multi-column index the ordering must be kept the same as of query to make the index useful.

1. Keep the equality comparison criteria first –

For example –

SELECT id from table WHERE type > 12 and type < 115 and userid = 12
Here type > 12 and type < 15 is range condition and userid = 12 is a equality comparison.

Let’s see how both will process internally: Range condition will find the first element of the range i.e 12 and then go up to the last element i.e. 115 and then it will return those set of values in between. Now in case of equality comparison, MySQL has to find the value i.e. 12 and return a single row, most probably it will be stored as B-Tree hence it will be more quick to traverse and find the result.

So modifying this query as –

SELECT id FROM table WHERE userid = 12 and type > 12 and type < 115

will use the created indexes in a better way.

1. Re-use existing multi-column index if your query is having a column sequence in the same order (skipping columns in between is allowed) –

This needs few examples for explanations. If there is a multi-column index on (C1, C2, C3, C4) and a query contains columns in WHERE condition in the following sequence – C2, C3, C4 then above index can be used in this query since the sequence is same irrespective of the fact that a column is skipped.

Similarly, the query including columns in WHERE clause as C1, C3, C4 will also use the index as the sequence of query and index is same BUT query with sequence C2, C1, C3, C4 will not be using this index as the sequence is not similar to that of the index.

Simply keep the sequence of columns in where condition same as that of any index if that index has all the columns in the where clause MySQL will pick that index automatically.

ARE THE INDEXES BEING USED?

Many times we create an index on single columns and assume that a query with multiple where conditions will be using the index but it does not. This results in slow queries even though you have created an index. In this situation, you are wasting an index, memory and having false hope of an index being used.

For help, you should use the EXPLAIN statement with your queries to check if indexes are being used or not.

Just put the word EXPLAIN before your query, you will get a row with details of indexes being used or possible indexes which can be used etc. Using EXPLAIN before your query doesn’t execute your query, so you can use it with UPDATE and even DELETE commands safely.

EXPLAIN SELECT id FROM table;
This will produce a row with columns such as Possible keys, type, Keys, Extra. By the values of these four columns, you can understand if your query is optimized and is using any index or not.

Type – This column can have the following values :

i) ALL: If your type column has this value it means your query is doing a full table scan to search your result which is bad. In short, there is no benefit of creating your index since it has to go through all the rows to search for the result. Hence you need to modify the index you have created or create a new one.

ii) const: This means your index is being used and MySQL will give the result very quick. This indicates your query is making equality comparison and the result set has only one row or the table had only one matching row which is the reason for this to be fast.

iii) range: Range index search. Your indexes are used to find the range of result set. This mostly shows up when you are looking for result set in between some range in where clause.

There are other types present as well which shows up when using joins or if your table doesn’t contain any data etc. which are mentioned in MySQL official page.

POSSIBLE KEYS –

This will contains comma-separated names of indexes which MySQL thinks can be used to fetch data for the query.

KEYS –

This contains the name of the index which MySQL actually used to fetch the result. By this column, you can verify if your query is using the intended index or not.

EXTRA –

This row shows the details if MySQL is doing some additional work even after using query or it just using the index or any other information. This column contains basic information but you should be alarmed if this column indicated that query is using ‘filesort’, ‘temporary filesort’. If this row contains something like ‘using index‘, ‘using index condition’ then your query is good and is utilizing the index properly. But if this column has value like ‘using temporary filesort;’ it means you need to optimize your query because MySQL is taking extra time to sort your result after fetching data from the index which takes extra time and hence speed will not be as expected from an index.

COVERING INDEX

Covering indexes means multi-columns indexes which have all the columns used in the query. The columns in covering index included all the columns which are used in where clause, order by clause, select clause. If all these columns are not more then 5-6 then covering index is a better option.

To create a covering index first include the columns of Where clause in the index sequentially as in the query then columns in order by clause and then the columns in the select statement.

WHEN MYSQL IGNORES INDEXES?

There are many cases when your indexes won’t be used. Considering the fact that before using the indexes MySQL optimizer decides if it needs to use an index or not if yes then which index will be appropriate and then it will select an index. Few situations which I have encountered where MySQL optimizer decides not to use any index are-

1. While using MySQL Functions- Using MySQL functions like DATE(), COUNT(), SUM() etc. does not allow MySQL to use index.

2. Using group by and order by on different columns in the same query will not use indexes even though you have created indexes on both the columns of order by and group by.

HOW TO MAKE MYSQL USE MY INDEX –

Mysql has its own optimiser which determines whether to use a index or not, if yes which index should be used. If you have created any special index for a query and you want MySQL to use that index you can do so by using USE INDEX(index_name), FORCE INDEX(index_name) within the query. It is a way of instructing MySQL to use a particular index and saving its time to analyze which index to use.

WHY NOT USE EXCESS/UNWANTED INDEX?

Whenever you insert or update any table the indexes of those tables are also updated. If you are doing updation/insertion very frequent in a table there are high chances that your operation will take more time and hence affecting the speed overall.

KEEP A CHECK ON INDEXES

As there are many conditions when MySQL will decide not to use the index hence Mysql maintains a list of indexes which are unused or rarely used for a long period of time. To find out just run the following query and you will get a list of indexes which are just using memory space and are of no use.

select * from sys.schema_unused_indexes;