MySQL Forums
Forum List  »  Optimizer & Parser

Re: At Performance Limit for 2 GB RAM?
Posted by: Jay Pipes
Date: July 22, 2005 02:06PM

Nick,

What you first want to do is isolate the amount of time that the query is taking versus the amount of time involved in transferring 10-20K records over the network (!). To do so, run the same query, but instead of returning the results, do a COUNT(*). If the result returns quickly, then it may be a setting related to the network packet size or possibly the client which can be tuned.

Also, of utmost importance is the lack of a PRIMARY KEY on the y_metadatatemp table. Having no index on this table, especially if this is an InnoDB table, is A Bad Thing. Attach an index to this table (which would essentially load the whole thing into a small amount of memory) and re-run the EXPLAIN with the COUNT(*) variant. Any changes?

Also, for adequate testing, ensure that you use a representative range of values in your testing for the stn_id constant. You may find that for queries matching a smaller number of rows for the constant in the y_statsinfo table, that a different query plan is utilized.

Let us know,

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: At Performance Limit for 2 GB RAM?
2841
July 22, 2005 02:06PM
3060
August 04, 2005 07:17PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.