MySQL Visual Explain

What is type index in MySQL EXPLAIN?

Performance Classification: bad

MySQL is traversing the whole index when you see the index type instead of using only a tiny part of it to get your result. But at least MySQL will then always use only the index to find and get all the columns, which is indicated by Using index in the Extra column. The performance will not be good but at least not as bad as when an index would not be used at all. This mostly happens when you have indexes that cover all the columns but their ordering is not optimal.

Examples

❌ When it is used

CREATE TABLE reviews (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    author bigint NOT NULL,
    comment varchar(255) NOT NULL,
    created_at datetime NOT NULL,
    INDEX (author, comment)
);

SELECT author, comment FROM reviews;

A common mistake is not using any conditions or LIMIT clause for the query and later doing this in the application code. For a small table, this may still be fast enough but it's getting slower and slower as the table and therefore also the size of the index grows.

CREATE TABLE grades (
   id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
   student_id bigint NOT NULL,
   course varchar(255) NOT NULL,
   points tinyint UNSIGNED NOT NULL,
   INDEX (points,course)
);

SELECT points, count(*) FROM grades WHERE course = 'CS50' GROUP BY points;

Sometimes, you've created an index with multiple columns but the column ordering within the index is not great: In this case, the course column must be first for filtering based on the condition and the points column last for grouping by that column. The index could still be used but all index entries had to be scanned to find only the ones matching the condition.

CREATE TABLE suppliers (
  id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(100) NOT NULL,
  address varchar(100) NOT NULL,
  INDEX (name,address)
);

SELECT address FROM suppliers WHERE name LIKE '%mysql%';

Like conditions with a leading wildcard can never use any index efficiently as: By definition, any column could match the condition so its not possible to only look at e.g. the last half of the index. All values have to be scanned! This typically triggers a much slower full-table scan but could be optimized here to scan only the index because all used table columns are part of the index.

How to optimize a query using index?

Fixing the index type is relatively easy because the conditions triggering it are simple. A good index for the query is missing. Follow this two-step process to get a much better-performing table access type:

Conclusion

The index type clearly signals that your query has severe performance problems. MySQL was able to make the query a little bit faster than a slow full-table scan because it was able to find an index with all columns included in your query. However, you still must create a better multi-column index to fix the performance problem and with a better index the query speed will also improve a lot.