脚本宝典收集整理的这篇文章主要介绍了php – MySQL语句需要花费多一点时间来解决,脚本宝典觉得挺不错的,现在分享给大家,也给大家做个参考。
select * From `ratings` order by id limIT 499500,500
每个查询都需要一分钟以上的时间,但是当我把表放到10万条记录上时,这个查询执行得很快.
正如我已经看到的,表中的100万条记录没有问题,因为在数据库表中,没有大记录的问题.
我在Stack Overflow问题How do I add indices to MySQL tables?的帮助下使用表中的id索引,但是我仍然遇到同样的问题.
***我正在使用CodeIgniter的项目.
create table ratings ( id int auto_increment Primary key,thing int null )engine=MyISAM; insert ratings (thing) values (null),(null),(null); insert ratings (thing) select thing from ratings; insert ratings (thing) select thing from ratings; insert ratings (thing) select thing from ratings; insert ratings (thing) select thing from ratings; insert ratings (thing) select thing from ratings; insert ratings (thing) select thing from ratings; insert ratings (thing) select thing from ratings; insert ratings (thing) select thing from ratings; insert ratings (thing) select thing from ratings; insert ratings (thing) select thing from ratings; insert ratings (thing) select thing from ratings; insert ratings (thing) select thing from ratings; insert ratings (thing) select thing from ratings; insert ratings (thing) select thing from ratings; insert ratings (thing) select thing from ratings; insert ratings (thing) select thing from ratings; insert ratings (thing) select thing from ratings; insert ratings (thing) select thing from ratings; insert ratings (thing) select thing from ratings;
我现在有4.7M行
select count(*),min(id),max(id) from ratings; +----------+---------+---------+ | count(*) | min(id) | max(id) | +----------+---------+---------+ | 4718592 | 1 | 4718592 | +----------+---------+---------+ select * from `ratings` order by id limit 499500,500; -- 1 second on a dumpy laptop
.
explain select * from `ratings` order by id limit 499500,500; +----+-------------+---------+------+---------------+------+---------+------+---------+----------------+ | id | select_tyPE | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+---------+----------------+ | 1 | SIMPLE | ratings | ALL | NULL | NULL | NULL | NULL | 4718592 | Using filesort | +----+-------------+---------+------+---------------+------+---------+------+---------+----------------+
.
explain select * from `ratings` where id>=499501 limit 500; +----+-------------+---------+-------+---------------+---------+---------+------+---------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+---------------+---------+---------+------+---------+-----------------------+ | 1 | SIMPLE | ratings | range | PRIMARY | PRIMARY | 4 | NULL | 4198581 | Using index condition | +----+-------------+---------+-------+---------------+---------+---------+------+---------+-----------------------+
道德的故事可能是使用where子句.
不能排除僵局的可能性.
以上是脚本宝典为你收集整理的php – MySQL语句需要花费多一点时间来解决全部内容,希望文章能够帮你解决php – MySQL语句需要花费多一点时间来解决所遇到的问题。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。