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.com>wrote: > > > 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
