mysql - Mysql Explain shows that query is using index when it shouldnt be according to Mysql doc

Question

I created a mysql multi column index on a transactions table. This index uses 3 columns as described in my rails schema :

  add_index "merchant_transactions", ["reference", "parent_ref", "kind"], name: "by_reference_parent_ref_kind", using: :btree

Now I have this active record query :

MerchantTransaction.where(reference: "2-9020", kind: "PLACE_BATCH")

which in pure sql gives :

"SELECT `merchant_transactions`.* FROM `merchant_transactions` WHERE `merchant_transactions`.`reference` = '2-9020' AND `merchant_transactions`.`kind` = 'PLACE_BATCH'"

Now from what I read about mysql and multi column indexing :

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). For more information, see Section 8.3.5, “Multiple-Column Indexes”.

To me, this means that the precedent query shouldnt be using the previous index.

However when I run EXPLAIN on my query MerchantTransaction.where(reference: "2-9020", kind: "PLACE_BATCH").explain under the keycolumn, I get by_reference_parent_ref_kind and under the Extra column I have Using index condition which seems to imply that the index is actually used.

How is that possible ?

标签： mysqlindexingexplain

Answer 1

它将使用索引，因为您在查询 ( reference) 中列出了最左边的列，即文档中的用例 (col1)。kind不通过索引搜索条件 ( ) 中的另一列。