![mysql optimizer trace mysql optimizer trace](http://azhangzhifeng.cn/img/alipay.jpg)
The reason for this somewhat confusing output (multiple occurances of "chosen:true") is that it is based on what the optimizer knows at the time it is written a "chosen:true" means more or less "this is the best access method seen so far". It is always the latest occurance of "chosen:true" that determines which access method is actually chosen. "table_scan": blocks within it that will show either "chosen:true" or "chosen:false" for scans and range access. I have some example snippets showing the optimizer seemingly using cost and row count to choose one index over both a range scan and a few other indexes: How expensive (in terms of disk access, processing, time, etc.) I assume it is just a guess on the respective storage engine's part of What is the genesis of the cost variable displayed in the trace? Is cost more important than row count or equally important?Ĥ. Of measurement (ex: Time, Disk Seeks, etc.)?ģ. Related to that question, what is cost exactly? Is there a unit Or that the row count was already accounted for/factored into in theĢ. Optimizer? I had the impression the row count did not directlyĬontribute to the optimizer's decision as much as the estimated cost, How is the row count more than merely informational to the
![mysql optimizer trace mysql optimizer trace](http://minsql.com/uploads/r21.png)
There a few questions I have about row count and cost.ġ. Results very helpful when combined with analyzing the source code, but I have been using optimizer tracing and have found the enabled=on turns on tracing, end_marker=off makes the trace a bit less human readable but JSON compliant and one_line=off is to make a pretty-printed trace: You can turn on and modify it's behavior using a session variable called optimizer_trace. So why did the plan change from table scan to range scan after inserting a few rows? Let's see what optimizer tracing has to report. | 1 | SIMPLE | orders | range | customer_id | customer_id | 10 | NULL | 26 | Using index condition | Mysql> /* Insert a few rows into orders */ | 1 | SIMPLE | orders | ALL | customer_id | NULL | NULL | NULL | 160 | Using where | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | Mysql> EXPLAIN SELECT * FROM orders WHERE customer_id 6 OR (customer_id 8 AND value = 500) The best way get a feeling of optimizer tracing is to give it a try. In other words: optimizer tracing is not a replacement for EXPLAIN. This gives us the freedom to improve tracing at a high pace without going through multi-release deprecation warnings etc. We will not guarantee backwards compatibility like we do for EXPLAIN. However, we're going to add more information when we find valuable things to add. The feature is not, and never will be, complete in the sense that it does not describe all choices the optimizer does. More coverage will be added as we go along. Which conditions are attached to each table (i.e., what hides behind "Using where" in EXPLAIN).The access methods applicable and why one of them was chosen.Important query transformations like IN to EXISTS.Trace is presented in JSON format which is easy to read both for humans and others.Ĭurrently, the optimizer trace includes short explanations for: MySQL optimizer has done during the process of making the Query
![mysql optimizer trace mysql optimizer trace](https://oss-emcsprod-public.modb.pro/image/editor/20210206-83214f07-2fb3-47d4-8342-49ad8d6ac73d.png)
This, but optimizer tracing is a printout of important decisions the The target users of this feature are developers and MySQL users experienced enough to understand the ins and outs of EXPLAIN. That's why we're introducing a great new feature in 5.6: Optimizer Tracing. Two almost identical queries, differing only in constant values, may produce completely different plans. Understanding why MySQL chooses a particular join order or why table scan is chosen instead of range scan is often very hard even for experienced MySQL users.