我的数据表结构
mysql> show columns from quote;
+--------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+----------------+
| symbol | char(8) | YES | | NULL | |
| date | date | YES | | NULL | |
| open | double | YES | | NULL | |
| high | double | YES | | NULL | |
| low | double | YES | | NULL | |
| close | double | YES | | NULL | |
| vol | double | YES | | NULL | |
| adj | double | YES | | NULL | |
| id | int(10) | YES | | NULL | |
| new_field_id | int(10) | NO | PRI | NULL | auto_increment |
+--------------+---------+------+-----+---------+----------------+
symbol是每个股票的代码,今天的close价格,高于年初到昨天最高收盘价的95%,我要选出这样的股票。
数据库说明,3000多只股票,100M,70万行。
我先找出:年初到昨天最高收盘价SELECT a.symbol,a.date,a.open,a.high,a.low,a.close,a.vol,a.adj FROM quote a,quote b where a.symbol=b.symbol AND a.close <=b.close and date>'2010-01-01' and date<'2010-07-28' GROUP BY a.symbol,a.date,a.open,a.high,a.low,a.close,a.vol,a.adj HAVING COUNT(b.id) =1 ORDER BY a.symbol,a.date,a.close desc;就这条sql查询,执行了几个小时,没有停,请问,我如何优化算法??
mysql> show columns from quote;
+--------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+----------------+
| symbol | char(8) | YES | | NULL | |
| date | date | YES | | NULL | |
| open | double | YES | | NULL | |
| high | double | YES | | NULL | |
| low | double | YES | | NULL | |
| close | double | YES | | NULL | |
| vol | double | YES | | NULL | |
| adj | double | YES | | NULL | |
| id | int(10) | YES | | NULL | |
| new_field_id | int(10) | NO | PRI | NULL | auto_increment |
+--------------+---------+------+-----+---------+----------------+
symbol是每个股票的代码,今天的close价格,高于年初到昨天最高收盘价的95%,我要选出这样的股票。
数据库说明,3000多只股票,100M,70万行。
我先找出:年初到昨天最高收盘价SELECT a.symbol,a.date,a.open,a.high,a.low,a.close,a.vol,a.adj FROM quote a,quote b where a.symbol=b.symbol AND a.close <=b.close and date>'2010-01-01' and date<'2010-07-28' GROUP BY a.symbol,a.date,a.open,a.high,a.low,a.close,a.vol,a.adj HAVING COUNT(b.id) =1 ORDER BY a.symbol,a.date,a.close desc;就这条sql查询,执行了几个小时,没有停,请问,我如何优化算法??
FROM quote q
WHERE `date`=date(now()) and
`CLOSE`>0.95*(SELECT max(`CLOSE`)
FROM quote
WHERE q.symbol=symbol AND `date` between date_add(date(now()),INTERVAL -dayofyear(now())+1 day) and date_add(date(now()),INTERVAL -1 DAY));
from quote a ,(
select symbol,max(close) as max_close
from quote
where date>'2010-01-01' and date<'2010-07-28'
group by symbol
) b
where a.symbol=b.symbol
and a.date='2010-07-29'
and a.close>=0.95*b.max_close创建复合索引(date,symbol)