List:General Discussion« Previous MessageNext Message »
From:Rick James Date:November 26 2012 6:59pm
Subject:RE: Query Plan Analyzer
View as plain text  
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.
> >
> >
Thread
Query Plan AnalyzerCabbar Duzayak22 Nov
  • RE: Query Plan AnalyzerZhigang Zhang22 Nov
    • Re: Query Plan AnalyzerCabbar Duzayak22 Nov
      • RE: Query Plan AnalyzerRick James26 Nov
  • Re: Query Plan AnalyzerJohan De Meersman22 Nov