0

I'm studying index on MySql and I'm trying to understand how they work.

I learn some basic commands, like how to create an index, how to check indexes on a table, how to use explain command etc.

My simple and stupid question is:

after I create and index, for example with 

CREATE INDEX index_first_name ON contacts (id, first_name, last_name);

should I do something else to tell MySql to "use" that index?

According to what I understood, MySql decides to use a specific "index" when there is a kind of "match" between the fields indexed and the "where" clause. Is is correct?

To take advantage of indexes, I just have to write a "standard" select like select first_name from contacts, right?

Thank you

MDP
  • 4,177
  • 21
  • 63
  • 119
  • 1
    You only get the advantage of indexes when your query uses the same columns (order can matter as well). In general, yes the index exists once you create it, there's nothing special, it's about the execution plan. https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html – William_Wilson Jun 29 '18 at 15:21
  • Hi @William_Wilson, thank you for your help. So the query in my example DON'T take advantage of my index, right? Because my SELECT query for first_name, unlike my index that take into account 3 fields. Is it correct? – MDP Jun 29 '18 at 15:24
  • 2
    A query without any filtering, grouping, ordering or joining will not benefit from indexes. – Shadow Jun 29 '18 at 15:41
  • @Shadow - One small exception: a "covering" index. This is where all the columns in the `SELECT` are in a single `INDEX` -- the query might use the index because it is "smaller", hence less stuff to go through to get the resultset. – Rick James Jun 30 '18 at 18:06

0 Answers0