CODEWITHSUNDEEP
mysqlsql
re1man
re1man

Reputation: 2377

mysql AND index

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?

Upvotes: 2

Views: 116

Answers (2)

Daniel Trebbien
Daniel Trebbien

Reputation: 39238

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.

Upvotes: 2

Artem Goutsoul
Artem Goutsoul

Reputation: 753

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

Upvotes: 1

Related Questions