mysql 查询增强,解释MySQL解释和建议以增强查询响应时间

我正在运行一个过滤下来的聚合查询,并希望得到一些关于如何获得更好的查询响应时间的反馈.

查询(运行,但平均为400秒)：

select data_date,sum(closeprice) from moving_avgs

where

symbol in (select distinct symbol from moving_avgs

where

ma200_close >= 5.00 and

ma200_volume >= 400000 and

data_date = (select min(data_date) from moving_avgs

where year(data_date) = 2007)

)

group by data_date;

我的EXPLAIN查询读取(格式化为在此环境中读取)：

id: 1

select_type: PRIMARY

table: moving_avgs

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 6250033

Extra: Using where; Using temporary; Using filesort

id: 2

select_type: DEPENDENT SUBQUERY

table: moving_avgs

type: unique_subquery

possible_keys: PRIMARY,symbol,data_date,ma200_close,ma200_volume

key: PRIMARY

key_len: 29

ref: func,const

rows: 1

Extra: Using where

id: 3

select_type: SUBQUERY

table: moving_avgs

type: index

possible_keys: NULL

key: data_date

key_len: 3

ref: NULL

rows: 6250033

Extra: Using where; Using index

我的my.ini [mysqld]& [myisamchk]部分阅读(在4GB双处理器AMD笔记本电脑上运行)：

[mysqld]

port = 3306

socket = /tmp/mysql.sock

skip-external-locking

key_buffer_size = 512M

max_allowed_packet = 20M

table_open_cache = 256

sort_buffer_size = 8M

read_buffer_size = 8M

read_rnd_buffer_size = 16M

myisam_sort_buffer_size = 256M

thread_cache_size = 8

query_cache_size= 132M

basedir=c:/wamp/bin/mysql/mysql5.5.24

log-error=c:/wamp/logs/mysql.log

datadir=c:/wamp/bin/mysql/mysql5.5.24/data

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8

[myisamchk]

key_buffer_size = 256M

sort_buffer_size = 256M

read_buffer = 4M

write_buffer = 4M

谢谢！