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:cabbar@stripped]
> Sent: Thursday, November 22, 2012 12:45 AM
> To: mysql@stripped
> Subject: Re: Query Plan Analyzer
>
> Is this a joke?
>
> On Thu, Nov 22, 2012 at 10:20 AM, Zhigang Zhang
> <zzgang2008@stripped>wrote:
>
> > By experience!
> >
> > -----Original Message-----
> > From: Cabbar Duzayak [mailto:cabbar@stripped]
> > Sent: Thursday, November 22, 2012 3:13 PM
> > To: mysql@stripped
> > 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.
> >
> >
