Sargable is short for “Search ARGument ABLE”. A condition is sargable when MySQL can use an index to find the matching rows directly, instead of reading every row and testing it.
The core rule:
> Keep the column alone on one side of the comparison. Put all the calculations on the other side.
Let us create a table big enough to see the difference.
query.sql
SQL
SET SESSION cte_max_recursion_depth = 20000;
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(80) NOT NULL,
signup_date DATE NOT NULL,
score INT NOT NULL,
KEY idx_email (email),
KEY idx_signup (signup_date),
KEY idx_score (score)
);
INSERT INTO customers (email, signup_date, score)
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 10000)
SELECT CONCAT('User', i, '@Example.com'), DATE_ADD('2025-01-01', INTERVAL MOD(i, 500) DAY), MOD(i * 7, 1000) FROM n;
ANALYZE TABLE customers;1. A function on the column
query.sql
SQL
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE YEAR(signup_date) = 2025;
Output
OUTPUT
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | customers | NULL | index | NULL | idx_signup | 3 | NULL | 9968 | 100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
1 row in settype = index and a big rows number mean that MySQL reads the whole index (or, without a covering index, the whole table, type = ALL) and runs YEAR() on every entry. The rows value is only an estimate. It changes a little between runs and between MySQL versions, so your numbers will not match exactly. Compare the type and key columns. The sargable form:
query.sql
SQL
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE signup_date >= '2025-01-01' AND signup_date < '2026-01-01';
Output
OUTPUT
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | customers | NULL | range | idx_signup | idx_signup | 3 | NULL | 7300 | 100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
1 row in setNow type = range and the key is idx_signup.
2. Arithmetic on the column
query.sql
SQL
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE score + 10 = 100;
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE score = 100 - 10;
Output
OUTPUT
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | customers | NULL | index | NULL | idx_score | 4 | NULL | 9968 | 100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | customers | NULL | ref | idx_score | idx_score | 4 | const | 10 | 100.00 | Using index |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in setMove the arithmetic to the other side. The second query uses idx_score with type = ref.
3. A leading wildcard
query.sql
SQL
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE email LIKE 'user77%';
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE email LIKE '%77@example.com';
Output
OUTPUT
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | customers | NULL | range | idx_email | idx_email | 322 | NULL | 111 | 100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | customers | NULL | index | NULL | idx_email | 322 | NULL | 9968 | 11.11 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in setA pattern that starts with fixed text can use the index. A leading % cannot.
4. Wrapping the column in LOWER or UPPER
LOWER(email) = 'user77@example.com' also disables the index. With a case-insensitive collation (the default), you do not need LOWER() at all:
query.sql
SQL
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE email = 'USER77@EXAMPLE.COM';
SELECT id, email FROM customers WHERE email = 'USER77@EXAMPLE.COM';
Output
OUTPUT
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | customers | NULL | ref | idx_email | idx_email | 322 | const | 1 | 100.00 | Using index |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set
+----+--------------------+
| id | email |
+----+--------------------+
| 77 | User77@Example.com |
+----+--------------------+
1 row in setThe comparison already ignores case, and the index is used. If you do need a function on a column, create a functional index (Functional Indexes).
5. Mismatched types
query.sql
SQL
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE email = 123;
Output
OUTPUT
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | customers | NULL | index | idx_email | idx_email | 322 | NULL | 9968 | 10.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in setA number compared with a text column forces a conversion of every row, so the index is useless (Type Conversion).
6. OR across different columns
query.sql
SQL
EXPLAIN FORMAT=TRADITIONAL SELECT id FROM customers WHERE score = 5 OR signup_date = '2025-03-01';
Output
OUTPUT
+----+-------------+-----------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
| 1 | SIMPLE | customers | NULL | index_merge | idx_signup,idx_score | idx_score,idx_signup | 4,3 | NULL | 30 | 100.00 | Using union(idx_score,idx_signup); Using where |
+----+-------------+-----------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
1 row in setMySQL can combine two indexes here (Using union), which is good. But if one of the two columns has no index, the whole query becomes a table scan. An OR is only as fast as its slowest branch.
7. NOT conditions and inequality
<>, NOT IN and NOT LIKE normally return most of the table, so MySQL often prefers a scan to an index. This is not wrong. It is the cheapest plan when most rows match.
A cheat sheet
| Slow (not sargable) | Fast (sargable) |
|---|---|
YEAR(d) = 2025 | d >= '2025-01-01' AND d < '2026-01-01' |
DATE(created_at) = '2026-07-01' | created_at >= '2026-07-01' AND created_at < '2026-07-02' |
price * 2 > 100 | price > 50 |
col + 1 = 10 | col = 9 |
LOWER(email) = 'a@b.c' | email = 'a@b.c' (with a _ci collation) |
name LIKE '%son' | A full-text index, or a reversed-text column with an index |
LEFT(name, 3) = 'Abc' | name LIKE 'Abc%' |
col = '5' (col is INT) | col = 5 |
How to check
Always put EXPLAIN in front of the query you care about (EXPLAIN). If key is NULL or type is ALL, the query is reading the whole table.
lightbulb
Small tables hide the problem
On 100 rows every query is fast. Test with a realistic number of rows, using a generated table (Generating Test Data).