mysql AND index

Question

I have an index structured as so:

BTREE merchant_id
BTREE flag

BTREE test    (merchant_id, flag)

I do a SELECT query as such :

 SELECT badge_id, merchant_id, badge_title, badge_class
FROM badges WHERE merchant_id = 1 AND flag = 1

what index would be better? Does it matter if they are in a seperate index?

Interesting question. Generally I would avoid adding the merchant_id-index as it is the left most part of the test-index. But if that is really faster for the select I don't know. However, adding more indexes adds a cost for all inserts so in total I think it's better to just add the two indexes. I tried to figure out how secondary indexes with multiple columns work but failed. I hope some one has the answer. — Andreas Wederbrand
– Andreas Wederbrand, Commented Jan 15, 2012 at 19:36

Daniel Trebbien · Accepted Answer · 2012-01-16 13:33:59Z

To answer questions such as "Which column would be better to index?", and "Is the query planner using a certain index to execute the query?", you can use the EXPLAIN statement. See the excellent article Analyzing Queries for Speed with EXPLAIN for a comprehensive overview of the use of EXPLAIN in optimizing queries and schema.

In general, where a query can be optimized by indexing one of several columns, a helpful rule of thumb is to index the column that is "most unique" or "most selective" over all records; that is, index the column that has the most number of distinct values over all rows. I am guessing that in your case, the merchant_id column contains the most number of unique values, so it should probably be indexed. You can verify that an index choice is optimal using EXPLAIN on the query for all variations.

Note that the rule of thumb "index the most selective column" does not necessarily apply to the choice of the first column of a composite (also called compound or multi-column) index. It depends on your queries. If, for example, employee_id is the most selective column, but you need to execute queries like SELECT * FROM badges WHERE flag = 17, then having as the only index on table badges the composite index (employee_id, flag) would mean that the query results in a full table scan.

Artem Goutsoul · Accepted Answer · 2012-01-15 20:10:48Z

1

Out of 3 indices you don't really need a separate merchant_id index, since merchant_id look-ups can use your "test" index.

More details: http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

answered Jan 15, 2012 at 20:10

Artem Goutsoul

7535 silver badges17 bronze badges

Collectives™ on Stack Overflow

mysql AND index

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related