CREATE TABLE `eos_softs` (
`id` int(10) NOT NULL auto_increment,
`name` varchar(50) NOT NULL default '',
`accessurl` varchar(50) default NULL,
`downloaded` int(20) default '0',
`picture` varchar(50) default NULL,
`source` varchar(25) default NULL,
`content` tinytext,
`type_id` int(5) default '0',
`url` tinyint(2) default '0',
`status` tinyint(2) default '1',
`keywords` varchar(50) default NULL,
`clicked` int(9) default '1',
`created` int(11) default NULL,
`modified` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=25 ;
以上是表结构,用EXPLAIN SELECT *
FROM eos_softs
ORDER BY id DESC
LIMIT 0 , 10 什么会出现filesort
`id` int(10) NOT NULL auto_increment,
`name` varchar(50) NOT NULL default '',
`accessurl` varchar(50) default NULL,
`downloaded` int(20) default '0',
`picture` varchar(50) default NULL,
`source` varchar(25) default NULL,
`content` tinytext,
`type_id` int(5) default '0',
`url` tinyint(2) default '0',
`status` tinyint(2) default '1',
`keywords` varchar(50) default NULL,
`clicked` int(9) default '1',
`created` int(11) default NULL,
`modified` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=25 ;
以上是表结构,用EXPLAIN SELECT *
FROM eos_softs
ORDER BY id DESC
LIMIT 0 , 10 什么会出现filesort
所以会出现filesort.想干掉filesort,如下语句:
set @i = 0;
select a.* from eos_softs as a
inner join (
select id,@i := @i+1 as counter from eos_softs order by id desc
) as b on a.id = b.id and (b.counter >=1 and b.counter <=10);
set @i = NULL;