Nick,
There's really no need for the metadatatemp join, as far as I can tell. You can reduce the query to:
SELECT
dich_value
, COUNT(*) as count
FROM y_statsinfo as stats
WHERE stn_id = 2500
GROUP BY dich_value;
with no effect. For this query, having an index on (stn_id, dich_value) would be beneficial, but only if the number of rows returned by the stn_id = 2500 filter is less than around 35% of the total rows in the table. If not, it would be faster to do a simple table scan. Either case, if you're working with the majority of 20M rows in the index, a sequential scan is about the best you can do...
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com