What is type const in MySQL EXPLAIN?

The const table access type (type column) identifies a special optimization: MySQL knows that this table with your used conditions can only have one result at most because you're filtering on the primary key or a unique index. So the matching row for this table is loaded directly at the start when the query is executed. Then, MySQL replaces the table's columns in the query with the loaded values and continues the normal execution. To sum up, the table has been replaced with constant (const) values in the query.

Examples

✅ When it is used

CREATE TABLE airlines (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    code char(2) NOT NULL,
    name varchar(255) NOT NULL,
    UNIQUE (code),
    INDEX (name)
);

SELECT * FROM airlines WHERE id = 4623;
SELECT * FROM airlines WHERE code = 'ZZ';
SELECT * FROM airlines WHERE code = 'ZZ' and name = 'MySQL Air';

These first two queries are executed with the const method because they filter based on the primary key or a unique key.

Similarly, the third one also uses the const optimization because it filters on the unique key: Any additional columns are unimportant because there still can only be at most one row matching the query's conditions.

❌ When it is not used

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

SELECT * FROM companies WHERE name = 'Oracle Corporation';

const is not used, although a unique key exists: All parts of a key have to be used and not a subset. This schema guarantees that each company name is unique within a country, but that does not imply that a company name is also unique worldwide.

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

SELECT * FROM books WHERE category = 'databases';
SELECT * FROM books WHERE name = 'Indexing Beyond the Basics';

These two queries won't use the const optimization because those queries don't use a primary or unique key. The name column has an index but that's unrelated: MySQL can't precisely know how many rows will match the conditions because there is no uniqueness guarantee, which is needed to use the const table access method. There could be many books named Indexing Beyond the Basics or just one. MySQL can't know this without a unique key on the name column.

CREATE TABLE employees (
  id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  fullname varchar(255) NOT NULL,
  UNIQUE (fullname)
);

SELECT * FROM employees WHERE fullname like 'Marcus P%';

The fullname column is compared with a like comparison that can match many rows instead of an equality comparison that would have guaranteed at most one result.

How to optimize a query using const?

Congrats! You've achieved a very very efficient of using an index. There is nothing to optimize for because this is the most efficient way of using an index in MySQL.

Conclusion

The const optimization is excellent for performance and will be used whenever all parts of a primary or unique key are checked to be equal to a specific value. Try always adding unique keys to tables to allow this optimization but remember that this is not always possible.