我的数据表结构
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查询,执行了几个小时,没有停,请问,我如何优化算法??
解决方案 »
- dedecms批量更改栏目的SQL语句
- alter table xxx drop xxx,如何判断可以避免在没有字段的情况下不提示
- 怎么把MYSQL的数据转移到MSSQL上呢?
- postgres的编码问题
- 在vb中连mysql除了odbc和myvbql还有其他连接方式吗
- 从5个表中select时候出错,为何要求SET SQL_BIG_SELECTS=1???
- 如何设置MySQL的自动断开连接时间?
- 菜鸟一个,刚学Mysql,向各位请教
- MYSQL运行很慢,想破了天都想不出是什么原因
- 数据库知识串讲
- mysql按某字段排序查询时,怎么样始终把某条记录放在第一个呢?
- 如何利用mysql select产生新表?
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)