Mysql – How does MySQL or PostgreSQL deal with multi-column indexes in ActiveRecord

indexMySQLpostgresql

I'm creating indexes for my models right now and I want to know how MySQL and PostgreSQL deal with an index with more than 1 column like:

add_index :users, [:username, :created_at]

That should utilize the index when I do a query like (I think):

User.where("username = ? and created_at > ?", 'jim', DateTime.now.yesterday)

But will it also utilize the index if I only use the username in a query?

User.where("username = ?", 'jim')

Best Answer

In PostgreSQL at least, multi-column indexes are usable for single-column searches on the left column, but not for single-column searches on the right column.

So: an index on (a,b,c) can be used for searches on a, a,b and a,b,c but not for searches on b,c or c.

If search patterns make individually usable indexes on all columns necessary and if the column values are small, people often create indexes a,b,c, b,c and c.

An alternative can be individual indexes on a, b, and c. PostgreSQL can use a bitmap index scan in many circumstances to combine the indexes, so a search with conditions on b and c will use both the indexes on b and c.

Rather than guess, the best thing to do is use EXPLAIN ANALYZE or the auto_explain module to examine the query plans and run-time stats from your queries, so you can see what's actually happening.

Related Solutions

Mysql – How are multiple indexes used in a query by MySQL

MySQL is capable of doing an INDEX MERGE which allows MySQL to search on both indexes and merge the results. Of course, the MySQL Query Optimizer is the arbitrator of this process.

From my perspective, just looking at your query and its WHERE clause, I would create a compound index on your table and an additional index. Which compound index, (a,b) or (b,a) ?

I would next check the cardinality of each column as follows:

SELECT COUNT(DISTINCT a) a_count FROM `table`;
SELECT COUNT(DISTINCT b) b_count FROM `table`;

Based on these counts

If a_count < b_count, then I would create compound index (a,b) and an index on b
If a_count > b_count, then I would create compound index (b,a) and an index on a
If a_count = b_count, then I would create one pair or other

For more information, please read the following links:

MySQL – Does Multi-Column Index Order Matter?

In your case it's a choice between bad and worse. gender is low selective (read MySQL has to read at least a half of index), if age comes first, gender part won't be used at all (because of < ). (gender, age) seems to be a bit better anyway.

Check with SHOW STATUS LIKE 'Handler%' which index makes MySQL to read less rows.

mysql> FLUSH STATUS;
mysql> SELECT name WHERE gender='M' and age<18;
mysql> mysql> show status like 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 38    |
| Handler_delete             | 8     |
| Handler_discover           | 0     |
| Handler_external_lock      | 154   |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 35    |
| Handler_read_key           | 62    |
| Handler_read_last          | 0     |
| Handler_read_next          | 153   |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 5947  |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 1     |
| Handler_write              | 617   |
+----------------------------+-------+

Best Answer

Related Solutions

Mysql – How are multiple indexes used in a query by MySQL

MySQL – Does Multi-Column Index Order Matter?

Related Question