What is type ALL in MySQL EXPLAIN?

MySQL is telling you with the ALL access type that it did not use any index to speed up your query: All rows from the table are loaded one after another, checked against your conditions and thrown away if they don't match until the requested number of rows are found - if you used a LIMIT. Either an index does not exist or was not selected because it can't be used or would be too slow to use.

Examples

❌ When it is used

CREATE TABLE courses (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(255) NOT NULL
);

SELECT * FROM courses WHERE name = 'Introduction to MySQL';

Obviously, an index won't be used and the full table has to be scanned if an index does not exist for any column used in the WHERE conditions.

CREATE TABLE medications (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(255) NOT NULL,
    price numeric(8,2) NOT NULL,
    publish_date date NOT NULL,
    INDEX (publish_date)
);

SELECT * FROM courses WHERE YEAR(publish_date) > 2000;
SELECT * FROM products WHERE price + 10 > 100;

In both cases, an index exists but can't be used because it has been obfuscated by transformations: When an operation is executed on the indexed column (like a function call or math), the index is no longer usable to speed up a query.

CREATE TABLE essays (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    author varchar(255) NOT NULL,
    content text NOT NULL,
    INDEX (author)
);

SELECT * FROM essays WHERE author LIKE '%Riley %';

A common misconception is LIKE queries: An index exists and will be used when searching for specific authors - wildcard searches are also enhanced by an index. However, the wildcard cannot be the first character in the search term - being the second, third or any later character is ok.

CREATE TABLE website_visits (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    path varchar(255) NOT NULL,
    happened_at timestamp NOT NULL,
    INDEX (path)
);

SELECT COUNT(*) FROM website_visits WHERE path = '/learning/explain';

Whenever a query is executed, MySQL has to estimate the performance for each usable index to choose the fastest one. However, scanning the table from start to finish is sometimes faster than using an index and loading rows from thousands of random positions specified by the index.

How to optimize a query using ALL?

The goal has to be to use an index to access the rows of the table by one of these strategies:

• Add an index for used columns if there isn't one yet
• Change your queries to no longer transform indexed columns or create a functional index

However, remember that preventing a full table scan is not always possible. In some cases, an index will never be used and you can't create a better index to fix it:

• LIKE comparisons with a leading wildcard will never use an index
• When large chunks of a table are loaded (e.g. min 5-10% of rows) not using an index is faster than using one

Conclusion

When seeing the ALL type, MySQL loads every table row. This is bad for performance and should be investigated to determine whether it can be fixed: You should create an index if none exists yet or look at the query to see whether you transformed an indexed column - a common mistake. The performance improvement will be massive if you get MySQL to use an index.