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