RE: Query Plan Analyzer
Yes and no. It takes a lot of experience to get beyond what EXPLAIN gives us. EXPLAIN says what it does, but usually one wants to know what to do. To that end, I have written some tips: http://mysql.rjweb.org/doc.php/ricksrots http://mysql.rjweb.org/doc.php/index1 But if you have one of the classic problems, you need to think out of the box. Examples: EAV, Pagination, Big deletes, nearest pizza parlors, lat/long searching, latest news articles, UUID indexing, picking a random row. The above links lead to other pages that discuss efficient approaches to those gnarly tasks. I find Profiling to be useless, since it seems to always have 95% of the time is in copying; this says nothing actionable. EXPLAIN is useful in confirming that the query will be executed in the way you think it ought to be -- Using this index or that, hitting the tables in a particular order, Using index (meaning that the data did not need to be touched), Using join buffer (often bad). I repeatedly tell people that using filesort/temp-table is not necessarily evil; think what it must take to do GROUP BY z ORDER BY x If you want an analysis of a particular query, please provide SHOW CREATE TABLE -- for engine, datatypes, and indexes SHOW TABLE STATUS -- for size EXPLAIN SELECT -- for what the optimizer's plan EXPLAIN PARTITIONS SELECT -- if partitioned. Oh, another note... Akiban's EXPLAIN is far more detailed than MySQL's. (But then, it is doing some tricky things.) -Original Message- From: Cabbar Duzayak [mailto:cab...@gmail.com] Sent: Thursday, November 22, 2012 12:45 AM To: mysql@lists.mysql.com Subject: Re: Query Plan Analyzer Is this a joke? On Thu, Nov 22, 2012 at 10:20 AM, Zhigang Zhang zzgang2...@gmail.comwrote: By experience! -Original Message- From: Cabbar Duzayak [mailto:cab...@gmail.com] Sent: Thursday, November 22, 2012 3:13 PM To: mysql@lists.mysql.com Subject: Query Plan Analyzer Hi All, Is there a way of looking at how mysql builds the query plan and executes it for a given query? EXPLAIN is definitely a useful tool, but it is not exact (shows approximations as far as I can see), furthermore I want something like how the predicates were applied (before or after JOINS), which indexes were used, etc. Basically, Oracle's EXPLAIN PLAN and SQL TRACE do this, and it is very very useful for optimizing queries. Don't get me wrong, I love mysql, but with mysql, you optimize queries more by trial and error, instead of understanding what exactly is going on. Thanks a ton. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Query Plan Analyzer
By experience! -Original Message- From: Cabbar Duzayak [mailto:cab...@gmail.com] Sent: Thursday, November 22, 2012 3:13 PM To: mysql@lists.mysql.com Subject: Query Plan Analyzer Hi All, Is there a way of looking at how mysql builds the query plan and executes it for a given query? EXPLAIN is definitely a useful tool, but it is not exact (shows approximations as far as I can see), furthermore I want something like how the predicates were applied (before or after JOINS), which indexes were used, etc. Basically, Oracle's EXPLAIN PLAN and SQL TRACE do this, and it is very very useful for optimizing queries. Don't get me wrong, I love mysql, but with mysql, you optimize queries more by trial and error, instead of understanding what exactly is going on. Thanks a ton. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query Plan Analyzer
Is this a joke? On Thu, Nov 22, 2012 at 10:20 AM, Zhigang Zhang zzgang2...@gmail.comwrote: By experience! -Original Message- From: Cabbar Duzayak [mailto:cab...@gmail.com] Sent: Thursday, November 22, 2012 3:13 PM To: mysql@lists.mysql.com Subject: Query Plan Analyzer Hi All, Is there a way of looking at how mysql builds the query plan and executes it for a given query? EXPLAIN is definitely a useful tool, but it is not exact (shows approximations as far as I can see), furthermore I want something like how the predicates were applied (before or after JOINS), which indexes were used, etc. Basically, Oracle's EXPLAIN PLAN and SQL TRACE do this, and it is very very useful for optimizing queries. Don't get me wrong, I love mysql, but with mysql, you optimize queries more by trial and error, instead of understanding what exactly is going on. Thanks a ton.
Re: Query Plan Analyzer
- Original Message - From: Cabbar Duzayak cab...@gmail.com Is there a way of looking at how mysql builds the query plan and executes it for a given query? EXPLAIN is definitely a useful tool, but it is not exact (shows approximations as far as I can see), furthermore I want something like how the predicates were applied (before or after JOINS), which indexes were used, etc. Basically, Oracle's EXPLAIN PLAN and SQL TRACE do this, and it is very very useful for optimizing queries. Don't get me wrong, I love mysql, but with mysql, you optimize queries more by trial and error, instead of understanding what exactly is going on. The Percona Toolset has pt-visual-explain and some other useful tools. There's also SHOW PROFILE in mysql itself, look at http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html for more about that. All in all, though, I'm not sure you can go quite as deep as you can in Oracle. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
