CODEWITHSUNDEEP
mysqlindexingexplain
David Geismar
David Geismar

Reputation: 3422

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

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 ?

Upvotes: 0

Views: 232

Answers (2)

Rick James
Rick James

Reputation: 142518

Given

WHERE reference = '...' AND kind = '...'

and

INDEX(reference, parent, kind)

The optimizer can use the index, but only the reference part.

On the other hand, if the only columns mentioned in the query are those three, then that index is "covering", so the Optimizer has another reason to use that index.

Please provide EXPLAIN SELECT .... In the Key_len column it will hint that only reference is used. In the Extra column, Using index is the clue that it is "covering".

This would be a better index for the current query, but might be worse for some other query:

INDEX(reference, kind, parent)

Upvotes: 0

slaakso
slaakso

Reputation: 9080

It will use the index as you have the leftmost column listed in query (reference), i.e use case (col1) from docs. The other column in condition (kind) is not searched through the index.

Upvotes: 1

Related Questions