Is there a way to determine if a certain query need to have an index?

I'd like to know if there is a command in mysql (phpmyadmin) to determine if a certain query needs an index.

For example I have this kind of query:

SELECT name FROM table_name WHERE lastname = 'jobs' ORDER BY date_joined DESC

Then, when I run this query, a message will pop up and say "You can add an index to column_name to increase the speed".

Query:

SELECT name
FROM table_name 
WHERE lastname = 'jobs' 
ORDER BY date_joined DESC ;

Indexes:

-
(lastname)

normal index for this type of queries, a simple index on the column in WHERE. It covers all queries that have WHERE last_name = 'some_vale' and will be quite efficient, assuming that the condition is "selective" enough, i.e. that you don't have millions of people with last name 'Jobs':

-
(lastname, date_joined)

a slightly different index, has the column in the ORDER BY as well. It will perform almost identically to the above index, except it will not require a sort. This is unlikely to be helpful, in most cases.

-
(lastname, date_joined, name)

a slightly better index, has the column from the ORDER BY and the SELECT lists. It will be slightly more efficient than the normal index, as it is "covering" completely the query. It won't need any sorting and all the values needed will be read from the index, without additional lookups in the main table. Whether this is needed, depends on the requirements about efficiency (will reducing the run time from 20 ms to 5 ms make any difference?).

It will however not be better when the query changes, for example by adding another column in the SELECTlist. So it is an index directed for a very narrow case.

What to do?

As a conclusion, you most probably only need a simple index on (last_name).


Do I need an index?

Yes, if these queries are common, the difference between not having an index and having one will be the difference between a full table scan and an index seek (with a few additional lookups in the table).

In terms of complexity, that's O(n) vs O(logn), where n is the size of the table, assuming the default B-tree indexes.

StackExchange Database Administrators Q#160516, answer score: 5