What is type eq_ref in MySQL EXPLAIN?

Performance Classification: good

The eq_ref type is a strategy MySQL uses to efficiently find rows in a join (or with a subquery) that target a primary or unique key. It's even the fastest approach, so there's no further need to look for optimizations with better indexes if you see this.

This strategy is always used when you have a condition on the target table of a join that is checking against a primary or unique key. However, you have to use conditions for all columns of that index!

Examples

✅ When it is used

CREATE TABLE doctors (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(100) NOT NULL
);
CREATE TABLE appointments (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    doctor_id bigint NOT NULL,
    start_at datetime NOT NULL,
    end_at datetime NOT NULL,
    FOREIGN KEY (doctor_id) REFERENCES doctors (id)
);

SELECT *
FROM appointments
JOIN doctors ON(doctors.id = appointments.doctor_id);

The eq_ref strategy will be applied because the join target (doctors table) is on the primary key - it would also be used when joining to a unique key on the target table.

CREATE TABLE country (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    isocode char(2) NOT NULL UNIQUE,
    name varchar(100) NOT NULL
);
CREATE TABLE airports (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    country char(2) NOT NULL,
    shortcode char(4) NOT NULL UNIQUE,
    name varchar(100) NOT NULL
);

SELECT * FROM country WHERE isocode IN(
    SELECT country FROM airports WHERE shortcode = 'AT01'
);

In the same way, it will be applied here for the country table because the subquery's results are checked against the unique key for the isocode column.

CREATE TABLE assignments (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    student_id bigint NOT NULL,
    shortcode char(4) NOT NULL UNIQUE,
    name varchar(100) NOT NULL
);
CREATE TABLE submissions (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    assignment_id bigint NOT NULL,
    name varchar(100) NOT NULL,
    grade tinyint UNSIGNED NOT NULL,
    UNIQUE(assignment_id, name)
);

SELECT *
FROM assignments
JOIN submissions ON(submissions.assignment_id = assignments.id)
WHERE submissions.name = 'final';

You can also constant values: This strategy is not limited to comparing the columns of the primary/unique key with other columns - or comparing them within the join condition. It's only important that all key columns are always used with equality conditions.

❌ When it is not used

CREATE TABLE profiles (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username varchar(15) NOT NULL UNIQUE
);
CREATE TABLE posts (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    profile_id bigint NOT NULL,
    message varchar(1000) NOT NULL,
    FOREIGN KEY (profile_id) REFERENCES profiles (id)
);

SELECT *
FROM profiles
JOIN posts ON(posts.profile_id = profiles.id)
WHERE username = 'mysql';

By definition, the eq_ref strategy can only be used when values are checked against primary or unique keys - and the posts.profile_id is only a standard index implicitly created by the foreign key constraint.

CREATE TABLE bookings (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    hotel_id bigint NOT NULL,
    code varchar(100) NOT NULL,
    UNIQUE(code,hotel_id)
);
CREATE TABLE complaints (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    hotel_id bigint NOT NULL,
    code varchar(100) NOT NULL,
    message varchar(100) NOT NULL,
    FOREIGN KEY (code,hotel_id) REFERENCES bookings (code,hotel_id)
);

SELECT * FROM complaints JOIN bookings USING(code);

The eq_ref strategy will only be used when all columns of a primary or unique key are used with an equality check. The mistake in missing the hotel_id for the join condition will lead to wrong results (when the same booking code is used at another hotel) and prevent the efficient eq_ref join operation from happening - but the used ref strategy is still fast.

How to optimize a query using `eq_ref`?

A query using eq_ref doesn't need to be optimized because it's the most efficient strategy when rows are searched based on another table or a subquery's result.

However, you can probably optimize other queries to use eq_ref when they're not using it:

Create unique indexes for columns that have unique values
Rewrite your queries to use all columns of primary and unique keys when filtering rows

Conclusion

The eq_ref type is the fastest strategy when searching for data in a table based on another - or a subquery's result. However, it will only be used when all primary or unique key columns are used in an equality check. Next time you create indexes, remember to use unique indexes when possible because they can give you a better performance when this table-searching strategy is used.