What is type ref in MySQL EXPLAIN?

Performance Classification: good

The ref strategy is the one you will see most used by MySQL and is always used when rows are read with an index and no more specialized access method is possible.

Examples

✅ When it is used

CREATE TABLE locations (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    country char(2) NOT NULL,
    description varchar(1000) NOT NULL,
    coordinates point NOT NULL,
    INDEX(country)
);

SELECT * FROM locations WHERE country = 'ZZ';

The strategy is used for simple lookups with an index.

CREATE TABLE customers (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(100) NOT NULL,
    INDEX(name)
);
CREATE TABLE orders (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    customer_id bigint NOT NULL,
    price numeric(8,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers (id)
);

SELECT *
FROM customer
JOIN orders ON(orders.customer_id = customers.id)
WHERE name = 'Sakila';

In the same way, it is also used for joins (and subqueries). The ref index strategy is used here to find all customers and for the joined orders table to find all rows for these customers.

CREATE TABLE users_tasks (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id bigint NOT NULL,
    task_id bigint NOT NULL,
    role varchar(100) NOT NULL,
    UNIQUE(user_id, task_id)
);

SELECT * FROM users_tasks WHERE user_id = 15;

It is not used when a primary or unique key is selected to find the rows - as the more efficient eq_ref strategy would be used. However, it is used if not all index columns of a primary/unique key are used with conditions.

❌ When it is not used

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

SELECT * FROM medications WHERE name = 'Painkiller 3000';

As the ref index strategy is used for indexes, it can't be used when no matching index exists.

CREATE TABLE transactions (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    reference varchar(100) NOT NULL,
    price numeric(8,2) NOT NULL,
    INDEX(reference)
);

SELECT * FROM medications WHERE reference = 9100166400;

Although an index exists, it can be used because the column and search term types do not match. MySQL implicitly typecast the column to an integer instead of warning you to compare both values. Hence, the index can't be used anymore.

How to optimize a query using `ref`?

It's already a good outcome if your query uses the ref strategy which means an index is used. However, the query can probably be tuned further to be more efficient - because the usage of an index with the ref strategy doesn't mean it is already perfect:

Check the filtered column for a high percentage value to ensure that most rows loaded with the index are also used and not thrown away after checking all conditions
Create a unique index for columns used in the conditions - if possible

Conclusion

Your index is used as shown by the ref strategy which is an important milestone. Congrats! But the index could probably be replaced by a better one when the filtered column indicates a low percentage of rows loaded and kept after all conditions have been evaluated.