A customer of ours had an interesting problem regarding a query
that was taking too long, around 55s. Looking at the query with
the query profiler we found that it was spending most of its time
in the "statistics" phase. Now the query was pretty complex, it
contained nearly 20 tables with INNER JOINs, LEFT JOINs and even
some subqueries. However the tables were small and fetching all
the data shouldn't have taken the 55 seconds the query was
taking. The problem was that the optimiser was spending too much
time evaluating and finding the optimal execution plan.
There are two options in MySQL with which you can control the
optimiser's behaviour a bit. The first one is
optimizer_prune_level. The pruner discards non-optimal execution
plans early without evaluating them fully. It is turned on by
default and is not recommended to turn off unless there's a
really good reason. For testing purposes we turned the pruner off
for this query, but …
Showing entries 1 to 1
Aug
11
2011
Showing entries 1 to 1