What is type range in MySQL EXPLAIN?

Performance Classification: good

The range condition is an access method MySQL always chooses when comparing indexed columns with one of the <, <=, >, >=, !=, <>, IS NOT NULL, BETWEEN, LIKE, IN() operators. This method is fast (compared to checking all rows without an index) but less efficient than selecting only specific values from an index with an equality condition. However, this is nothing you can optimize as you sometimes have to search for a range of values and not just a single one.

Examples

✅ When it is used

CREATE TABLE students (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(100) NOT NULL,
    age int NOT NULL,
    INDEX (age)
);

SELECT * FROM students WHERE age > 19;
SELECT * FROM students WHERE age < 23;
SELECT * FROM students WHERE age between 19 and 23;

Obviously, the range method is used when you query an indexed column with range operators like >, >=, <, <= and BETWEEN.

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

SELECT * FROM conferences WHERE name LIKE 'MySQL%';

Additionally, the range method is used for LIKE conditions when you have a few characters (or just one) and then a wildcard. Because internally the condition here will be rewritten as the range condition name >= 'MySQL' AND name < 'MYSQLM': It's searching for everything greater than or equal to the string you provided and less than the string that would be the first that doesn't match anymore.

CREATE TABLE tasks (
    id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title varchar(100) NOT NULL,
    priority tinyint UNSIGNED NOT NULL,
    INDEX(priority)
);

SELECT * FROM tasks WHERE priority IS NOT NULL;

Similarly, any IS NOT NULL condition is rewritten as a range condition: The range will be from the first possible not NULL value to the last one. In this example, the condition is rewritten to priority BETWEEN 0 AND 255.

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

SELECT * FROM professions WHERE name != 'technology';

An unequal condition != (or its other notation <>) is a range condition too - which is unexpected. However, exactly as in the former example the condition is rewritten to check the entire possible range of the column and exclude only the specified value when traversing all index entries.

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

SELECT * FROM projects WHERE id IN(18,43,912);

The IN() operation is also a range access in MySQL - even if it seems strange at first: Each value in the (18,43,912) list of values is handled as a separate range and the index is used to find the matching rows. It was implemented as a range operation as all three numbers could result in many matching rows (if it wasn't a primary key). There isn't any specific reason behind it because it could at the same the be implemented as ref access - someone just decided years ago that this is how it should work.

❌ When it is not used

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

SELECT * FROM inventory WHERE remaining < 10;

The range access only works on columns with an index.

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

SELECT * FROM patients WHERE name LIKE '%Marc%';

A common issue is wildcard patterns with a leading wildcard: MySQL can't rewrite these patterns to a range condition because every possible string would be included, as the first character is allowed to be anything. So this condition can't be improved by an index at all.

How to optimize a query using `range`?

Primarily, in most cases there isn't any need to optimize this part of a table. A range access is just exported technical information in which way the index is utilized to speed up loading the row(s) for this table.

However, if the range is very broad you should try to optimize it: Checking every index entry in that range and loading the corresponding row takes some time. It's minimal time spent for a single row or a couple ones but when the range grows to tens or hundreds of thousands rows you may see the performance being impacted. Try to find a way to reduce the rows being affected in that range by shrinking it or create a good multi-column index for more efficient filtering.

Conclusion

The range method is always used on your index when you use a range condition. It's a fast operation you don't have to and can't optimize as it's the only way MySQL can validate these conditions. However, the used range should not be too large as checking hundreds of thousands index entries will still be slow.