![]() ![]() " Using where" tells me that a condition is evaluated after fetching a row from the table, but does not tell me what condition. | 1 | SIMPLE | t20 | ref | a | a | 5 | | 1 | Using where Using index |Īs optimizer developer, it catches my eye that some tables have " Using where" and others don't. | 1 | SIMPLE | t19 | ref | a | a | 5 | | 1 | Using where Using index | | 1 | SIMPLE | t18 | ref | a | a | 5 | | 1 | Using where Using index | | 1 | SIMPLE | t17 | ref | a | a | 5 | | 1 | Using where Using index | | 1 | SIMPLE | t16 | ref | a | a | 5 | | 1 | Using where Using index | | 1 | SIMPLE | t15 | ref | a | a | 5 | | 1 | Using where Using index | | 1 | SIMPLE | t14 | ref | a | a | 5 | | 1 | Using where Using index | | 1 | SIMPLE | t13 | ref | a | a | 5 | | 1 | Using where Using index | | 1 | SIMPLE | t12 | ref | a | a | 5 | | 1 | Using where Using index | | 1 | SIMPLE | t11 | ref | a | a | 5 | test.t1.a | 1 | Using where Using index | | 1 | SIMPLE | t10 | ref | a | a | 5 | test.t9.a | 1 | Using where Using index | | 1 | SIMPLE | t9 | ref | a | a | 5 | test.t1.a | 1 | Using where Using index | | 1 | SIMPLE | t8 | ref | a | a | 5 | test.t1.a | 1 | Using index | | 1 | SIMPLE | t7 | ref | a | a | 5 | test.t1.a | 1 | Using index | | 1 | SIMPLE | t6 | ref | a | a | 5 | test.t1.a | 1 | Using index | | 1 | SIMPLE | t5 | ref | a | a | 5 | test.t1.a | 1 | Using index | | 1 | SIMPLE | t4 | ref | a | a | 5 | test.t1.a | 1 | Using index | | 1 | SIMPLE | t3 | ref | a | a | 5 | test.t2.a | 1 | Using where Using index | ![]() | 1 | SIMPLE | t2 | ref | a | a | 5 | test.t1.a | 1 | Using where Using index | | 1 | SIMPLE | t1 | index | a | a | 5 | NULL | 2 | Using where Using index | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | In my case, it would be "please do not trace greedy search".Īs an example, let's consider twenty tables, t1.t20, all similar to this one: It would be nice if I could tell the system: "please do not trace this and that". If I set to a high value, to see what I want, greedy search will be traced which will possibly exceed the amount of memory I can afford on this machine. What if I want to see the trace of what happens in the Optimizer after greedy search is complete? If I set to a low value, it will trim greedy search and what follows. Greedy search ends up being the greatest part of the trace. When there are many tables to join, as I said above the trace can grow a lot, because the Optimizer evaluates many possible join orders (using an algorithm known as " greedy search"): each evaluated join order is mentioned in the trace. ![]() One last benefit is that when I have finished reading, quitting " less" makes the trace go away from the terminal, it does not linger on my screen forever, does not occupy the terminal's display history. I have all " less" commands under hand: can save to a file, can search for a regular expression match, search forward or backward. I often raise it in my session, to 1 megabyte, which is enough for most queries in my daily work: Which has a default of 16 kilobytes yes, it's a low value, to protect the innocent. Up to gigabytes in some pathological cases! To avoid hogging memory then, a trace will never grow beyond the value of the session variable. An SQL statement which gives the optimizer a lot of work (for example, by joining many tables) will generate a large trace. Today I will be giving some must-have tips related to handling big traces.įirst thing to know, a trace lives in main memory (internally it is allocated on the heap or free store of the MySQL Server). It's good to see it mature now I remember that Sergey Petrunia did the first prototype back in March 2009! The Optimizer Tracing feature can help understanding what the Optimizer is doing it is available since milestone 5.6.3, announced October 3rd at Oracle Open World (here is the changelog). I recommend reading his article, as it presents this new feature in a simple, easy-to-read manner. In this blog post, my colleague Jørgen Løland described a new feature of MySQL 5.6: Optimizer Tracing. ![]()
0 Comments
Leave a Reply. |