select distinct n.id,n.headLine from news n
inner join stocknewsrelation snr on snr.news_id=n.id
inner join stock s on snr.stock_id=s.id
inner join stockindexrelation sir on sir.stock_id=s.id
inner join indexinfo i on sir.index_id=i.id
where i.indexCode='HSI' and n.newsUpdateTime >= '2008-8-10'
order by n.newsUpdateTime desc limit 5;相关的表有news,stock,indexinfo,stocknewsrelation,stockindexrelation五张表.
外键都建立了索引,news的newsUpdateTime也建立了索引.
但是查询速度执行的却很慢,后辍后relation的表是连接表.
news到stock到indexinfo,都是多对多的关系.本以来是索引没建好,可是select id from news order by newsUpdateTime desc limit 5;
速度很快14ms结束.
news下大概有8W条数据.
数据库是MySQL5.0,不知道那位能帮着找一下原因?
inner join stocknewsrelation snr on snr.news_id=n.id
inner join stock s on snr.stock_id=s.id
inner join stockindexrelation sir on sir.stock_id=s.id
inner join indexinfo i on sir.index_id=i.id
where i.indexCode='HSI' and n.newsUpdateTime >= '2008-8-10'
order by n.newsUpdateTime desc limit 5;相关的表有news,stock,indexinfo,stocknewsrelation,stockindexrelation五张表.
外键都建立了索引,news的newsUpdateTime也建立了索引.
但是查询速度执行的却很慢,后辍后relation的表是连接表.
news到stock到indexinfo,都是多对多的关系.本以来是索引没建好,可是select id from news order by newsUpdateTime desc limit 5;
速度很快14ms结束.
news下大概有8W条数据.
数据库是MySQL5.0,不知道那位能帮着找一下原因?
select distinct n.id,n.headLine from news n
inner join stocknewsrelation snr on (snr.news_id=n.id) and (n.newsUpdateTime >= '2008-8-10')
inner join stock s on snr.stock_id=s.id
inner join stockindexrelation sir on sir.stock_id=s.id
inner join indexinfo i on (sir.index_id=i.id) and (i.indexCode='HSI')
order by n.newsUpdateTime desc limit 5;
可是explain后只使用了主键索引,并没有使用我定义的索引.
indexSIMPLE|n|ref|PRIMARY,newskeys,newsIdkeys|PRIMARY|386|etnetchina.snr.news_id|1|Using where麻烦看一下问题出在那里,调试了好久.