0

I have question if I have tables with 3 columns (firstname, lastname , address ) as string/varchar(255)

and I have composite my_idx with 2 columns

CREATE INDEX my_idx ON my_table (firstname,lastname)

if I use sql , will it use my defined index ?

select * from my_table where address="zzz" and firstname="xxxx" and lastname="yyyy"

or should I use index columns as first left most condition 

select * from my_table where  firstname="xxxx" and lastname="yyyy" and address="zzz"

Thank you

Denny
  • 449
  • 4
  • 17

1 Answers1

1

First of all: if you prepend your Query with the keyword "EXPLAIN" it will print out all the indices it may use and which one MySQL choose. From my understanding, yes it will use the index. The order of the fields in the Query is not relevant.

What matters is the order in the Index, but only if you are not providing all fields in the Query (or applying a function to the value or using e.g. the like operator for the rest of a string). If for example you only queried for lastname, the index can not be used. If you only queried for firstname, the index will be used. If you queried for firstname and address, the index will be used and so on...

Martin Schneider
  • 3,268
  • 4
  • 19
  • 29
  • The order in the `INDEX` matters in some other cases -- `WHERE firstname LIKE 'M%' AND lastname='Schneider'` needs `INDEX(lastname, firstname)`. `=` column(s) first; the usage of the index stops with a "range" (such as that `LIKE`). – Rick James Dec 14 '17 at 20:50
  • Yes, i ommitted that case in the last paragraph. That even extends to the case when a function is applied to the value, like `TRIM(lastname) = 'James'`, then the index will also not be used. – Martin Schneider Dec 15 '17 at 08:40
  • but shouldn't the use of `firstname LIKE 'M%'`still use the index, while `'firstname LIKE '%n'`would stop it? https://stackoverflow.com/questions/2481528/mysql-like-performance-boost#2481550 – Martin Schneider Dec 15 '17 at 08:55
  • `LIKE` _without_ a leading wildcard is treated as a "range", hence can be optimized somewhat -- not as good as `= constant`, but not as bad as using a function or `LIKE` with leading wildcard. [_More discussion_](http://mysql.rjweb.org/doc.php/index_cookbook_mysql) – Rick James Dec 15 '17 at 15:20