Bug #100649 Should we change the default value of optimizer_search_depth

• Submitted: 26 Aug 2020 18:23
• Modified: 27 Aug 2020 12:29
• Reporter: YIGONG HU
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S5 (Performance)
• Version: 5.6
• OS: Any (18.04)
• CPU Architecture: x86 (64bit)

[26 Aug 2020 18:23] YIGONG HU

Description:
The default value of optimizer_search_depth is 62, which means that it will perform exhaustive search for best query plan. If the select join involve too many tables, the search time could be very long. I use the sysbench to create 20 tables and create a select-join query which involves all of them, the explain time of select join is about 5ms. I change the optimizer_search_depth to 0 and the explained time reduce to 1ms. I also test the select-join with two tables, the result shows that the compilation time for both optimizer_search_depth = 62 and optimizer_search_depth = 0 is 1ms. The result seems to suggest that setting the default value of optimizer_search_depth to 0 would be better. Should we consider changing the default value to 0?

How to repeat:
-- Create and populate the table:
Install sysbench 1.0
sysbench --mysql-socket=sock_path --mysql-db=test --table-size=500 --tables=20 --threads=1 --max-time=10 --report-interval=10  select_random_ranges.lua prepare 

-- Run the select query 
explain select count(1) from  sbtest1 b  join sbtest2 c on b.pad=c.pad join sbtest3 d on c.pad=d.pad join sbtest4 e on e.pad=c.pad join sbtest5 f on f.pad=e.pad join sbtest6 g on g.pad=c.pad join sbtest7 h on h.pad=b.pad join sbtest8 i on i.pad=g.pad join sbtest9 j on j.pad=i.pad join sbtest10 k on k.pad=d.pad join sbtest11 l on l.pad=b.pad join sbtest12 m on m.pad=d.pad join sbtest13 n on e.pad=n.pad join sbtest14 o on f.pad=o.pad;

Suggested fix:
I would suggest changing the default value to 0.

[27 Aug 2020 12:29] MySQL Verification Team

Hi Mr. HU,

Thank you for your bug report.

However, this is not a bug. 

A default value is chosen in such a manner as to meet the medium of majority of queries among the majority of our users and customers. Also, EXPLAIN speed is totally irrelevant.

Since, this value can be changed, both for the server and for each individual query, a default value will remain as long as most of our community is happy with it.

Not a bug.