Composite Indexes in MySQL

I’m sure many have came across this scenario when you have to consider scalability with respect to data growth. For instance you have a database table that contains a million or more rows and you have to query from it often; you have to ensure that the system can provide data in a short amount of time. Also, take note that while the database engine is querying your data, the longer it takes, the more resources it eats up.

A few days ago I was having an issue with one of my projects, it has something to do with performance and optimization. I was querying data from a database table that contained roughly 1.6 million records and it took around 50-120 seconds to for the operation to complete.

Consider this database-table structure

This is just a flat mysql table using innodb engine.

Indexes provide fast lookups in the table so that data retrieval will be faster when you query data using indexed columns.

Consider that the table contains roughly 1.6 million records and run this query:
[cc lang=”mysql” tab_size=”2″ lines=”-1″]
SELECT
t.user_id,
SUM(t.direction = “i”) AS ‘num_in’,
SUM(t.direction = “o”) AS ‘num_out’
FROM tbl_user_reports t
WHERE t.bound_time BETWEEN ‘2011-02-01’ AND ‘2011-02-28’
GROUP BY t.user_id
HAVING t.user_id IS NOT NULL
ORDER BY num_in DESC
LIMIT 10;
[/cc]

It takes at least 60 seconds to finish this query considering the current table structure and the indexes.

To help track down why the query is slow, I used the ‘explain’ mysql command to investigate the query and what I can do to make the operation perform faster. (link: MySQL Explain Explained)

I came across the need for ‘Composite Indexes’. Composite Indexes is an index that is composed of 2 or more columns of a table. Looking at the query earlier, it uses 2 columns in the criteria which are: bound_time and direction. (link: Multiple-Column Indexes)

To create a composite index, use the mysql script:
[cc lang=”mysql” tab_size=”2″ lines=”-1″]
ALTER TABLE `tbl_user_reports` ADD INDEX `report_user_io` (`bound_time`, `direction`);
[/cc]

After adding a composite index, I tried executing the select query again and it took 0.02 seconds.

I hope this solution helps.