SQL OPTIMIZER_COSTS

OPTIMIZER_COSTS overrides the default cost model constants MySQL applies during its cost-based plan selection phase. Each constant represents an estimated relative cost of a low-level operation (reading a block from disk, evaluating a row, creating a temporary table, and so on). By altering these numbers you can bias the optimizer toward or away from certain strategies, such as preferring index scans over full table scans when I/O is expensive, or favouring in-memory operations in RAM-heavy environments.The variable is dynamic and can be set at GLOBAL or SESSION scope. When set at GLOBAL scope it affects all new sessions; an active session can override the global defaults by setting the SESSION value. You supply a comma-separated list of name=value pairs. Omitted names keep their current values. All numeric values are floating-point numbers greater than zero.Because the variable directly influences plan generation, changes can have large performance implications. Always benchmark in a non-production environment first, use SESSION-level settings for experimentation, and persist only well-tested GLOBAL values in my.cnf.

• io_block_read_cost (FLOAT) - Relative cost of reading one block from disk.
• memory_block_read_cost (FLOAT) - Cost of reading one block already resident in memory.
• cpu_tuple_cost (FLOAT) - CPU cost of processing one row.
• cpu_index_tuple_cost (FLOAT) - CPU cost of processing one index entry.
• memory_temptable_create_cost (FLOAT) - Cost to create an in-memory temporary table.
• memory_temptable_row_cost (FLOAT) - Cost per row inserted into an in-memory temporary table.
• disk_temptable_create_cost (FLOAT) - Cost to create a disk-based temporary table.
• disk_temptable_row_cost (FLOAT) - Cost per row inserted into a disk temporary table.
• key_compare_cost (FLOAT) - Cost of comparing two index keys.
• key_lookup_cost (FLOAT) - Cost of looking up one row via an index.
• Note - Supply only the constants you wish to override; others retain their last value.

• Each SET statement immediately updates the in-memory cost constants
• No rows are returned
• Subsequent queries in the session (or all new sessions, if GLOBAL) are optimized using the new numbers

• Performance tuning after EXPLAIN shows sub-optimal plans.
• Testing how different hardware profiles affect optimizer choices.
• Forcing the optimizer to avoid large temporary tables when RAM is scarce.
• Encouraging index usage on slow spinning disks by inflating io_block_read_cost.

• Forgetting to quote the name=value list.
• Providing unknown constant names, which raises ER_WRONG_ARGUMENTS.
• Setting GLOBAL values in production without prior benchmarking.
• Expecting existing sessions to adopt new GLOBAL settings automatically (they do not).