Description:
The optimizer may choose an inefficient ordering index for a query containing selective equality predicates together with ORDER BY ... LIMIT 1.
The test table has the following relevant indexes:
* idx_flow_id(flow_id)
* idx_start_time(start_time)
For the test query, flow_id='FLOW_TARGET' matches approximately 5,000 rows out of 5,000,000 rows. However, none of those rows satisfies the remaining predicates, so the final result is empty.
The expected efficient execution strategy is:
1. Use idx_flow_id to retrieve approximately 5,000 candidate rows.
2. Apply the remaining predicates.
3. Sort the remaining rows by start_time.
4. Return at most one row.
Because the final result is empty, the sort cost is negligible.
However, the optimizer chooses idx_start_time in order to avoid an explicit sort and to potentially stop early because of LIMIT 1.
This strategy is significantly slower. Since no row satisfies all predicates, the early-termination optimization cannot take effect. MySQL must scan the ordering index extensively, perform a large number of row lookups, and evaluate the remaining predicates before determining that the result is empty.
When idx_start_time is excluded by using the following optimizer hint:
/*+ NO_INDEX(t idx_start_time) */
MySQL uses the more selective access path and the query completes significantly faster.
The optimizer appears to overestimate the benefit of using the ordering index for ORDER BY ... LIMIT 1, while underestimating the cost of scanning and filtering rows when the additional predicates have little or no correlation with the ordering index.
How to repeat:
DROP DATABASE IF EXISTS order_limit_bug;
CREATE DATABASE order_limit_bug;
USE order_limit_bug;
CREATE TABLE t_order_limit_bug (
id BIGINT NOT NULL AUTO_INCREMENT,
app_id VARCHAR(32) NOT NULL,
flow_id VARCHAR(32) NOT NULL,
flow_version VARCHAR(32) NOT NULL,
instance_status VARCHAR(16) NOT NULL,
start_time DATETIME(3) NOT NULL,
payload VARCHAR(256) DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_flow_id (flow_id),
KEY idx_start_time (start_time)
) ENGINE=InnoDB;
-- Generate 5,000,000 rows with a single INSERT ... SELECT statement.
--
-- Every 1,000th row has flow_id = 'FLOW_TARGET', so idx_flow_id
-- matches approximately 5,000 rows.
--
-- These rows intentionally do not satisfy the remaining predicates:
-- app_id = 'APP_TARGET'
-- flow_version = 'V_TARGET'
-- instance_status = 'processing'
--
-- Therefore, the final query result is empty.
INSERT INTO t_order_limit_bug
(app_id, flow_id, flow_version, instance_status, start_time, payload)
SELECT
CASE WHEN MOD(seq,1000)=0
THEN 'APP_OTHER'
ELSE CONCAT('APP_',LPAD(MOD(seq,100),3,'0')) END,
CASE WHEN MOD(seq,1000)=0
THEN 'FLOW_TARGET'
ELSE CONCAT('FLOW_',LPAD(MOD(seq,32000),5,'0')) END,
CASE WHEN MOD(seq,1000)=0
THEN 'V_OTHER'
ELSE CONCAT('V_',LPAD(MOD(seq,20),2,'0')) END,
CASE WHEN MOD(seq,1000)=0 THEN 'success'
WHEN MOD(seq,10)=0 THEN 'processing'
ELSE 'finished' END,
TIMESTAMPADD(
MICROSECOND,
seq * 1000,
TIMESTAMP('2025-01-01 00:00:00.000')
),
RPAD('x',256,'x')
FROM (
SELECT d0.n+d1.n*10+d2.n*100+d3.n*1000+d4.n*10000+
d5.n*100000+d6.n*1000000+1 AS seq
FROM
(SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d0
CROSS JOIN
(SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d1
CROSS JOIN
(SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d2
CROSS JOIN
(SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d3
CROSS JOIN
(SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d4
CROSS JOIN
(SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d5
CROSS JOIN
(SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d6
) numbers
WHERE seq <= 5000000;
-- Refresh InnoDB optimizer statistics after loading the test data.
ANALYZE TABLE t_order_limit_bug;
-- Test 1:
-- Display the execution plan selected without optimizer hints.
--
-- In the affected environment, the optimizer chooses idx_start_time
-- to satisfy ORDER BY start_time ASC and avoid an explicit sort.
EXPLAIN
SELECT t.id
FROM t_order_limit_bug AS t
WHERE t.app_id='APP_TARGET'
AND t.flow_id='FLOW_TARGET'
AND t.flow_version='V_TARGET'
AND t.instance_status='processing'
ORDER BY t.start_time ASC
LIMIT 1;
-- Execute the default plan and collect actual runtime statistics.
--
-- Because no row satisfies all predicates, the LIMIT 1 early-stop
-- optimization cannot take effect. The plan scans a large number of
-- entries from idx_start_time and evaluates the remaining predicates.
EXPLAIN ANALYZE
SELECT t.id
FROM t_order_limit_bug AS t
WHERE t.app_id='APP_TARGET'
AND t.flow_id='FLOW_TARGET'
AND t.flow_version='V_TARGET'
AND t.instance_status='processing'
ORDER BY t.start_time ASC
LIMIT 1;
-- Test 2:
-- Exclude idx_start_time and collect actual runtime statistics.
--
-- This causes the optimizer to use the more selective idx_flow_id access
-- path, which reads approximately 5,000 candidate rows before applying
-- the remaining predicates. Although this plan may require an explicit
-- sort, it completes significantly faster than the default plan.
EXPLAIN ANALYZE
SELECT /*+ NO_INDEX(t idx_start_time) */ t.id
FROM t_order_limit_bug AS t
WHERE t.app_id='APP_TARGET'
AND t.flow_id='FLOW_TARGET'
AND t.flow_version='V_TARGET'
AND t.instance_status='processing'
ORDER BY t.start_time ASC
LIMIT 1;
Suggested fix:
Improve the cost estimation for ordering-index access paths used to satisfy ORDER BY ... LIMIT.
In particular, the optimizer should consider:
1. The selectivity of equality predicates that are not part of the ordering index.
2. The estimated correlation, or lack of correlation, between those predicates and the ordering-index columns.
3. The expected number of ordering-index entries that must be scanned before finding a row satisfying all residual predicates.
4. The possibility that no qualifying row exists, in which case the early-termination benefit of LIMIT 1 is unavailable.
5. The cost of clustered-index row lookups and residual predicate evaluation for each entry scanned through the secondary ordering index.
6. The low cost of sorting a small candidate set obtained through a significantly more selective index.
The optimizer should compare the estimated cost of:
selective index scan + residual filtering + small sort
against:
ordering index scan + repeated row lookups + residual filtering
rather than preferring the ordering index mainly because it avoids a filesort and may theoretically stop early.
A possible conservative approach would be to reduce the estimated early-termination benefit when the filtering predicates are not covered by the ordering index and their correlation with the index order is unknown.
Description: The optimizer may choose an inefficient ordering index for a query containing selective equality predicates together with ORDER BY ... LIMIT 1. The test table has the following relevant indexes: * idx_flow_id(flow_id) * idx_start_time(start_time) For the test query, flow_id='FLOW_TARGET' matches approximately 5,000 rows out of 5,000,000 rows. However, none of those rows satisfies the remaining predicates, so the final result is empty. The expected efficient execution strategy is: 1. Use idx_flow_id to retrieve approximately 5,000 candidate rows. 2. Apply the remaining predicates. 3. Sort the remaining rows by start_time. 4. Return at most one row. Because the final result is empty, the sort cost is negligible. However, the optimizer chooses idx_start_time in order to avoid an explicit sort and to potentially stop early because of LIMIT 1. This strategy is significantly slower. Since no row satisfies all predicates, the early-termination optimization cannot take effect. MySQL must scan the ordering index extensively, perform a large number of row lookups, and evaluate the remaining predicates before determining that the result is empty. When idx_start_time is excluded by using the following optimizer hint: /*+ NO_INDEX(t idx_start_time) */ MySQL uses the more selective access path and the query completes significantly faster. The optimizer appears to overestimate the benefit of using the ordering index for ORDER BY ... LIMIT 1, while underestimating the cost of scanning and filtering rows when the additional predicates have little or no correlation with the ordering index. How to repeat: DROP DATABASE IF EXISTS order_limit_bug; CREATE DATABASE order_limit_bug; USE order_limit_bug; CREATE TABLE t_order_limit_bug ( id BIGINT NOT NULL AUTO_INCREMENT, app_id VARCHAR(32) NOT NULL, flow_id VARCHAR(32) NOT NULL, flow_version VARCHAR(32) NOT NULL, instance_status VARCHAR(16) NOT NULL, start_time DATETIME(3) NOT NULL, payload VARCHAR(256) DEFAULT NULL, PRIMARY KEY (id), KEY idx_flow_id (flow_id), KEY idx_start_time (start_time) ) ENGINE=InnoDB; -- Generate 5,000,000 rows with a single INSERT ... SELECT statement. -- -- Every 1,000th row has flow_id = 'FLOW_TARGET', so idx_flow_id -- matches approximately 5,000 rows. -- -- These rows intentionally do not satisfy the remaining predicates: -- app_id = 'APP_TARGET' -- flow_version = 'V_TARGET' -- instance_status = 'processing' -- -- Therefore, the final query result is empty. INSERT INTO t_order_limit_bug (app_id, flow_id, flow_version, instance_status, start_time, payload) SELECT CASE WHEN MOD(seq,1000)=0 THEN 'APP_OTHER' ELSE CONCAT('APP_',LPAD(MOD(seq,100),3,'0')) END, CASE WHEN MOD(seq,1000)=0 THEN 'FLOW_TARGET' ELSE CONCAT('FLOW_',LPAD(MOD(seq,32000),5,'0')) END, CASE WHEN MOD(seq,1000)=0 THEN 'V_OTHER' ELSE CONCAT('V_',LPAD(MOD(seq,20),2,'0')) END, CASE WHEN MOD(seq,1000)=0 THEN 'success' WHEN MOD(seq,10)=0 THEN 'processing' ELSE 'finished' END, TIMESTAMPADD( MICROSECOND, seq * 1000, TIMESTAMP('2025-01-01 00:00:00.000') ), RPAD('x',256,'x') FROM ( SELECT d0.n+d1.n*10+d2.n*100+d3.n*1000+d4.n*10000+ d5.n*100000+d6.n*1000000+1 AS seq FROM (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d0 CROSS JOIN (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d1 CROSS JOIN (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d2 CROSS JOIN (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d3 CROSS JOIN (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d4 CROSS JOIN (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d5 CROSS JOIN (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d6 ) numbers WHERE seq <= 5000000; -- Refresh InnoDB optimizer statistics after loading the test data. ANALYZE TABLE t_order_limit_bug; -- Test 1: -- Display the execution plan selected without optimizer hints. -- -- In the affected environment, the optimizer chooses idx_start_time -- to satisfy ORDER BY start_time ASC and avoid an explicit sort. EXPLAIN SELECT t.id FROM t_order_limit_bug AS t WHERE t.app_id='APP_TARGET' AND t.flow_id='FLOW_TARGET' AND t.flow_version='V_TARGET' AND t.instance_status='processing' ORDER BY t.start_time ASC LIMIT 1; -- Execute the default plan and collect actual runtime statistics. -- -- Because no row satisfies all predicates, the LIMIT 1 early-stop -- optimization cannot take effect. The plan scans a large number of -- entries from idx_start_time and evaluates the remaining predicates. EXPLAIN ANALYZE SELECT t.id FROM t_order_limit_bug AS t WHERE t.app_id='APP_TARGET' AND t.flow_id='FLOW_TARGET' AND t.flow_version='V_TARGET' AND t.instance_status='processing' ORDER BY t.start_time ASC LIMIT 1; -- Test 2: -- Exclude idx_start_time and collect actual runtime statistics. -- -- This causes the optimizer to use the more selective idx_flow_id access -- path, which reads approximately 5,000 candidate rows before applying -- the remaining predicates. Although this plan may require an explicit -- sort, it completes significantly faster than the default plan. EXPLAIN ANALYZE SELECT /*+ NO_INDEX(t idx_start_time) */ t.id FROM t_order_limit_bug AS t WHERE t.app_id='APP_TARGET' AND t.flow_id='FLOW_TARGET' AND t.flow_version='V_TARGET' AND t.instance_status='processing' ORDER BY t.start_time ASC LIMIT 1; Suggested fix: Improve the cost estimation for ordering-index access paths used to satisfy ORDER BY ... LIMIT. In particular, the optimizer should consider: 1. The selectivity of equality predicates that are not part of the ordering index. 2. The estimated correlation, or lack of correlation, between those predicates and the ordering-index columns. 3. The expected number of ordering-index entries that must be scanned before finding a row satisfying all residual predicates. 4. The possibility that no qualifying row exists, in which case the early-termination benefit of LIMIT 1 is unavailable. 5. The cost of clustered-index row lookups and residual predicate evaluation for each entry scanned through the secondary ordering index. 6. The low cost of sorting a small candidate set obtained through a significantly more selective index. The optimizer should compare the estimated cost of: selective index scan + residual filtering + small sort against: ordering index scan + repeated row lookups + residual filtering rather than preferring the ordering index mainly because it avoids a filesort and may theoretically stop early. A possible conservative approach would be to reduce the estimated early-termination benefit when the filtering predicates are not covered by the ordering index and their correlation with the index order is unknown.