MENU


Query Optimizer

The query optimizer's job is to find an efficient plan for executing an SQL query. Because the performance difference between a "good" plan and a "bad" plan can be orders of magnitude — seconds versus hours or even days — most optimizers, including MySQL's, perform an exhaustive search among all possible evaluation plans for join queries. The number of plans investigated grows exponentially with the number of tables referenced, which is rarely a problem for fewer than 7 to 10 tables but can make optimization itself the performance bottleneck for larger queries.

To generate execution plans, the optimizer uses a cost model based on compiled-in default "cost constants," together with a database of cost estimates stored in the server_cost and engine_cost tables in the mysql system database. These tables are configurable at any time, making it possible to adjust the estimates the optimizer uses when constructing execution plans.

System Variables

Several system variables control how exhaustive the optimizer's search is. Fewer plans investigated means less time compiling a query, but also a greater risk of missing the truly optimal plan.
SELECT @@optimizer_switch;
SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

Reads or changes the optimizer flag set. Each command in the SET form is flag_name=on or flag_name=off.


SELECT @@optimizer_switch;

@@optimizer_switch
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
The individual flags, with their defaults, are: batched_key_access (off), block_nested_loop (on), condition_fanout_filter (on), derived_condition_pushdown (on), derived_merge (on), engine_condition_pushdown (on), index_condition_pushdown (on), use_index_extensions (on), index_merge (on), index_merge_intersection (on), index_merge_sort_union (on), index_merge_union (on), use_invisible_indexes (off), prefer_ordering_index (on), mrr (on), mrr_cost_based (on), duplicateweedout (on), firstmatch (on), loosescan (on), semijoin (on), skip_scan (on), materialization (on), subquery_materialization_cost_based (on), and subquery_to_derived (off).

Optimizer Hints

Optimizer hints apply on a per-statement basis, written as a /*+ ... */ comment immediately after the leading keyword of the statement (or subquery) they target:
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
FROM t3 WHERE f1 > 30 AND f1 < 33;

Disables range optimization for the named indexes on t3.

SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;
INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);
SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') */ 1;
SELECT /*+ RESOURCE_GROUP(USR_default) */ name FROM people ORDER BY name;

Hints can enable/disable join algorithms per table, disable index condition pushdown, force or forbid merging a derived table, set a session variable for the duration of one statement, or bind a statement to a resource group.

A query block can be named with QB_NAME so other hints can target it by name, which is especially useful for controlling join order and semijoin strategy across subqueries:
SELECT
/*+ JOIN_PREFIX(t2, t5@subq2, t4@subq1)
JOIN_ORDER(t4@subq1, t3)
JOIN_SUFFIX(t1) */
COUNT(*) FROM t1 JOIN t2 JOIN t3
WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);
The available optimizer hints are: BKA/NO_BKA, BNL/NO_BNL, DERIVED_CONDITION_PUSHDOWN/NO_DERIVED_CONDITION_PUSHDOWN, GROUP_INDEX/NO_GROUP_INDEX, HASH_JOIN/NO_HASH_JOIN, INDEX/NO_INDEX, INDEX_MERGE/NO_INDEX_MERGE, JOIN_FIXED_ORDER, JOIN_INDEX/NO_JOIN_INDEX, JOIN_ORDER, JOIN_PREFIX, JOIN_SUFFIX, MAX_EXECUTION_TIME, MERGE/NO_MERGE, MRR/NO_MRR, NO_ICP, NO_RANGE_OPTIMIZATION, ORDER_INDEX/NO_ORDER_INDEX, QB_NAME, RESOURCE_GROUP, SEMIJOIN/NO_SEMIJOIN, SKIP_SCAN/NO_SKIP_SCAN, SET_VAR, and SUBQUERY.

USE/IGNORE/FORCE INDEX/KEY

tbl_name [[AS] alias] [index_hint_list]

index_hint_list:
    index_hint [, index_hint] ...

index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | IGNORE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  | FORCE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
    index_name [, index_name] ...
Index hints tell the optimizer which of a table's indexes to prefer, avoid, or require:
SELECT * FROM t1 USE INDEX (c1,c2) WHERE c1=1;

Restricts the optimizer to considering only c1 and c2's indexes. Specifying an empty index_list for USE INDEX means "use no indexes."

Statistics

The column_statistics data dictionary table stores histogram statistics about column values, used by the optimizer when constructing execution plans. Histogram management is performed with the ANALYZE TABLE statement. Because column_statistics is part of the data dictionary, it is not directly accessible; histogram information is instead exposed through INFORMATION_SCHEMA.COLUMN_STATISTICS, a view built on the dictionary table. 

-- Adjust optimizer flags
SET SESSION optimizer_switch = 'mrr_cost_based=off';

-- Per-statement hints
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
    FROM t3 WHERE f1 > 30 AND f1 < 33;

SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ name
    FROM people ORDER BY name;

-- Force the optimizer to use specific indexes
SELECT * FROM t1 USE INDEX (c1,c2) WHERE c1=1;
SELECT * FROM t1 IGNORE INDEX (c1) WHERE c1=1;
SELECT * FROM t1 FORCE INDEX (c1) WHERE c1=1;

-- Refresh histogram statistics used by the optimizer
ANALYZE TABLE t1 UPDATE HISTOGRAM ON c1 WITH 100 BUCKETS;
SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
    WHERE TABLE_NAME = 't1';