取总数:
Select SQL_BUFFER_RESULT count(col1) as all_ID from foods;
开始: start = 0
每页长度: page_length = 16
总页数: pages = all_ID/page_length
最后一页的记录数: odd = all_ID%page_length查询当前页:
set @rownum=0;
select SQL_BUFFER_RESULT id,title,url,img,price
from foods,
( select my_id from
( select id as my_id,(@rownum:=@rownum+1) as row_id from foods
order by modified desc
) as tmp
where row_id between '@{int:1+start}'
and '@{int:start+page_length}'
) as temp
where my_id=id实际效果:
http://www.dfwgw.com/dGFva2UvbGlzdF9hbGwuaHRtbA==url.html表结构参考:
CREATE TABLE `foods` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`url` varchar(100) NOT NULL,
`catalog` int(11) NOT NULL DEFAULT '0',
`img` varchar(120) DEFAULT NULL,
`price` double DEFAULT '0',
`des` text,
`modified` datetime NOT NULL,
`sold_num` int(11) NOT NULL DEFAULT '0',
`location` varchar(28) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `catalog` (`catalog`),
KEY `modified` (`modified`),
KEY `sold_num` (`sold_num`),
FULLTEXT KEY `title` (`title`)
)
Select SQL_BUFFER_RESULT count(col1) as all_ID from foods;
开始: start = 0
每页长度: page_length = 16
总页数: pages = all_ID/page_length
最后一页的记录数: odd = all_ID%page_length查询当前页:
set @rownum=0;
select SQL_BUFFER_RESULT id,title,url,img,price
from foods,
( select my_id from
( select id as my_id,(@rownum:=@rownum+1) as row_id from foods
order by modified desc
) as tmp
where row_id between '@{int:1+start}'
and '@{int:start+page_length}'
) as temp
where my_id=id实际效果:
http://www.dfwgw.com/dGFva2UvbGlzdF9hbGwuaHRtbA==url.html表结构参考:
CREATE TABLE `foods` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`url` varchar(100) NOT NULL,
`catalog` int(11) NOT NULL DEFAULT '0',
`img` varchar(120) DEFAULT NULL,
`price` double DEFAULT '0',
`des` text,
`modified` datetime NOT NULL,
`sold_num` int(11) NOT NULL DEFAULT '0',
`location` varchar(28) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `catalog` (`catalog`),
KEY `modified` (`modified`),
KEY `sold_num` (`sold_num`),
FULLTEXT KEY `title` (`title`)
)
为什么不直接 (取第101页)
select * from foods order by modified desc limit 1600,16;
能解释一下吗？
@{int:1+start}当前页的开始位加1，第一页时为1+0=1；
@{int:start+page_length}当前页的开始位加页长度，第一页时为0+16=16
第一页时就是1到16行，第二页时就是17到32行
相当于
where row_id between '1' and '16' 或 where row_id between '17' and '32'
select id as my_id,(@rownum:=@rownum+1) as row_id from foods order by modified desc 先保证查询到最少的数据，tmp_table_size=64M也能快速运行，接下来的操作都是在内存中进行，如果没有其他操作锁表，查询基本上能在一秒内完成。当然这跟硬件也有很大的关系，目前服务为 dell T300，内存加到4G，系统是64位的CentOS5.4，最高时支撑过500人在线，效果还行！
我做了一个试验，
当表中数据 有50多万条的时候 显然是limit快当表中数据 有100多万条的 时候 仍然是limit快 而且快多了我的表结构：
CREATE TABLE `i_userfriends` (
`user_id` int(10) unsigned NOT NULL DEFAULT '0',
`friend_id` int(10) unsigned NOT NULL DEFAULT '0',
`group_id` int(11) NOT NULL DEFAULT '0',
`re` varchar(50) DEFAULT NULL,
`isOpen` tinyint(1) unsigned NOT NULL DEFAULT '1',
`seeType` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`user_id`,`friend_id`),
KEY `ix_user_id_re` (`user_id`,`re`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
你的 select id as my_id,(@rownum:=@rownum+1) as row_id from foods
order by modified desc
还是执行 全表扫描