select
id,title,date_format(from_unixtime(update_ts),'%Y-%m-%d %H:%i:%s')
from
photo
force index(uid)
where
uid in (
select to_id from MobileRecordAttention force index(from_update)
where from_id=20000 and status =0 order by update_ts desc
)
order by update_ts desc limit 10,100;表结构:CREATE TABLE `MobileRecordAttention` (
`from_id` int(10) NOT NULL DEFAULT '0',
`to_id` int(10) NOT NULL DEFAULT '0',
`status` tinyint(1) NOT NULL DEFAULT '0',
`create_ts` int(10) NOT NULL DEFAULT '0',
`update_ts` int(10) NOT NULL DEFAULT '0',
`group_id` int(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`from_id`,`to_id`),
KEY `from_id` (`from_id`,`status`,`create_ts`),
KEY `to_id` (`to_id`,`status`,`create_ts`),
KEY `from_update` (`from_id`,`status`,`update_ts`)
) ENGINE=MyISAM ;CREATE TABLE `photo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`update_ts` int(10) NOT NULL,
`uid` int(10) NOT NULL,
`title` varchar(45) NOT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`)
) ENGINE=MyISAM ;谢谢!!!!!
id,title,date_format(from_unixtime(update_ts),'%Y-%m-%d %H:%i:%s')
from
photo
force index(uid)
where
uid in (
select to_id from MobileRecordAttention force index(from_update)
where from_id=20000 and status =0 order by update_ts desc
)
order by update_ts desc limit 10,100;表结构:CREATE TABLE `MobileRecordAttention` (
`from_id` int(10) NOT NULL DEFAULT '0',
`to_id` int(10) NOT NULL DEFAULT '0',
`status` tinyint(1) NOT NULL DEFAULT '0',
`create_ts` int(10) NOT NULL DEFAULT '0',
`update_ts` int(10) NOT NULL DEFAULT '0',
`group_id` int(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`from_id`,`to_id`),
KEY `from_id` (`from_id`,`status`,`create_ts`),
KEY `to_id` (`to_id`,`status`,`create_ts`),
KEY `from_update` (`from_id`,`status`,`update_ts`)
) ENGINE=MyISAM ;CREATE TABLE `photo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`update_ts` int(10) NOT NULL,
`uid` int(10) NOT NULL,
`title` varchar(45) NOT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`)
) ENGINE=MyISAM ;谢谢!!!!!
select id,title,photo.update_ts,date_format(from_unixtime(photo.update_ts),'%Y-%m-%d %H:%i:%s') from photo,MobileRecordAttention where photo.uid=MobileRecordAttention.to_id and MobileRecordAttention.from_id=20000 and MobileRecordAttention.status=0
order by photo.update_ts desc limit 10,100
id,title,date_format(from_unixtime(update_ts),'%Y-%m-%d %H:%i:%s')
from
photo inner join (select to_id from MobileRecordAttention
where from_id=20000 and status =0
) b on photo.uid=b.to_id
order by update_ts desc limit 10,100;
id,title,date_format(from_unixtime(update_ts),'%Y-%m-%d %H:%i:%s')
from
photo inner join (select to_id from MobileRecordAttention
where from_id=20000 and status =0
) b on photo.uid=b.to_id
order by update_ts desc limit 10,100;
你们的:1 PRIMARY <derived2> ALL 30 Using temporary; Using filesort
1 PRIMARY photo ref uid uid 4 b.to_id 11951
2 DERIVED MobileRecordAttention ref PRIMARY,from_id,from_update from_update 5 14 我的:1 SIMPLE MobileRecordAttention ref PRIMARY,from_id,to_id,from_update from_update 5 const,const 14 Using temporary; Using filesort
1 SIMPLE photo ref uid uid 4 test.MobileRecordAttention.to_id 12