Index - Chicken soup for your queries

You are stuck in the continuous, long-lasting, taking-forever-to-run queries, hence, feeling stuck in life, brace yourself, index has come to save the day!

1. Definition
In short, an index of SQL database is similar with index of a book.

2. How Mysql uses indexes
In a SELECT statement, what Mysql does without index is to perform a full table scan, which means it will begin with the first row and then read through the entire table to find the relevant rows.

If it takes 1 second to read 1 record, it takes 100 seconds to read the whole table with 100 records. It is actually much much faster for a SELECT statement to be executed with Mysql, databases in real life usually tend to be much much larger than only 100 records.

With index, data is mapped to indexes which is mostly stored in BTREE, help reduce significantly the amount of query execution time.

Example (based on Scuti knowledge base):

When you select a row in table with 200.000 records, if it takes 0.001s to access 1 record, it will take 0.001 x 200.000 = 200s <=> 3 minutes to loop through the whole table (relative assumption).

With index, retrieving data is conducted by searching indexes on BTREE, which means:

The average block when search in B-tree is log base 2 of 200.000 ~= 17 blocks.

The result is we only need access to 17 block to find the records we want.

If accessing to block take 0.001s so the total time we need is 0.001 x 17 = 0.017s.

Please note that this example applies to unsorted data case. If your data is sorted, it is much easier to seek for your desired records. In summary, using index is important.

3. CREATE INDEX Syntax

• Create index: CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (column1, column2, ...);
• Drop index: DROP INDEX index_name ON table_name;
• Show index: SHOW INDEX FROM table_name;

4. Types of common MYSQL index

• UNIQUE: A UNIQUE index creates a constraint such that all values in the index must be distinct.
• FULL TEXT: Index which is often used efficiently for varchar type columns.
• HASH: A hash table uses a hash function to compute an index into an array of buckets or slots, from which the desired value can be found. 

5. Multi-column indexes
You can add an index for many columns, it’s called composite index, or multi-column index. An index can contain up to 16 columns.

• If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

• When creating a multi-column index, remember to follow left-most prefix principle or else the index will not take effect.
• Example: If you have a WHERE `col3` = ‘something’ clause, a multicolumn index (col1, col2, col3) will not work.

• To have an efficient composite index, count the number of matching rows for each criterion and order the columns in the index in that order. For detailed information, please refer: https://www.percona.com/blog/2009/06/05/a-rule-of-thumb-for-choosing-column-order-in-indexes/

6. When not to use index.

• Don't randomly add indexes.
• It will slow things down on insert/delete/update operations. 
• Each time a column is updated, its indexes also need to be maintained.
• Don’t add too many indexes
• What MYSQL does in a query is that it will look into all the indexes and try to find the most optimum index, so as a consequence, the more indexes you add, the longer it takes.
• Consider creating index on values that has a "big domain" only. 
• Don’t create index in a column that only has “Yes” and “No” values.

Specific cases that index will not work

• When there’s ” <>, !=, IS NOT NULL” in the where condition
• When there’s “LIKE '%blah'  in the where condition
• When there’s function call DATE(columnName) = '...', LOWER(columnName) = '...', HOUR(columnName) = '...', etc

If you liked this article

Let's subscribe the updates of Scuti!

Share on Google Plus

About Anonymous

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.