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 ?
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.