On 8/20/2014 11:04 AM, Jim wrote:
> Without going into specific details on queries...
> Using mysql 5.1 as provided with CentOS6, I've noticed some queries
> providing what I can best explain as inconsistent optimization. The
> database can be quieted to just controlled queries and at times the same
> query will return very quickly when at other times may take minutes.
> I don't see the same behavior with mysql5.0 under CentOS5. The same
> queries on the same data returns quickly consistently.
> When the queries run slowly they show in a process list as either in a
> "copy to temp table" or "sending data" state. At first I thought query
> restructuring to avoid the copy to temp table was a path to a solution,
> but now I don't think so since the same query changed so that it no
> longer needs a temp table will sit in the "sending data" state for a
> long time.
> The queries do eventually come back with correct results, but it takes
> minutes rather than milliseconds (sometimes slow; sometimes fast).
> Have others seen this behavior? Any explanations?
> Any reading to point to for further understanding?
Fluctuations in query times can be the results of configuration mistakes
(like creating a 1GB query cache or a tiny InnoDB Buffer Pool), or data
changes (did you add or remove or change a bunch of rows), or query
patterns (did you add or remove terms from your WHERE clauses, did you
change which columns were in your SELECT clause, ... ).
To know why a query is doing what it is doing, you need to ask the
Optimizer. The Optimizer is that part of the server that works out the
most efficient way to go get the data you are asking for and how to
process that data once it is pulled from disk or cache.
This is the purpose of the EXPLAIN operator. Just put that word before
SELECT and see what you get. An explanation of how to interpret an
EXPLAIN report is here in the manual (you are reading the manual, right?)
That will give you a starting place. After that, you can refer to the
other sections of the "Optimization" chapter to see what you can or
should be changing to improve your performance.
You should also need to learn a little bit about the topic of "index
statistics" as those are what the Optimizer uses to develop its
for "ANALYZE TABLE determines index cardinality...")
Feel free to ask the list any questions that may arise in your research.
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN