MySQL大有潜力...

| 1 Comment | No TrackBacks
160万行的MyISAM表,PRIAMRY为自增的int格式id,  在对常用group by, order by字段加上索引后,处理为未加的25分之一。
加上索引后, select语句使用子查询max()精确查询最新单条记录,处理时间大约为0.3秒,使用force index(PRIAMRY)后,where单条件对PRIMARY进行order by id desc limit 1, 处理时间大约为0.03秒

No TrackBacks

TrackBack URL: http://blog.suchasplus.com/mt/mt-tb.cgi/188

1 Comment

select * from t force index(PRIMARY) where hostname=xxx order by id desc limit 1

select * from table where id = (select max(id) from table where host_name =
'blablabla')

建个联合索引,(host_name,timestamp)
然后select host_name,max(timestamp) from dd1 group by host_name;
参考,http://dev.mysql.com/doc/refman/5.1/en/loose-index-scan.html

Leave a comment