表结构:Create Table: CREATE TABLE `sina_weibo_status` (
`sid` bigint(20) NOT NULL DEFAULT '0',
`text` varchar(255) DEFAULT NULL,
`source` varchar(255) DEFAULT NULL,
`truncated` int(1) DEFAULT NULL ,
`thumbnail_pic` varchar(255) DEFAULT NULL,
`bmiddle_pic` varchar(255) DEFAULT NULL,
`original_pic` varchar(255) DEFAULT NULL,
`uid` bigint(20) DEFAULT NULL COMMENT,
`retweeted_status` bigint(20) DEFAULT '0',
`in_reply_to_status_id` bigint(20) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`aid` int(10) NOT NULL COMMENT,
`category` int(10) NOT NULL,
`status` tinyint(4) NOT NULL DEFAULT '0',
`retweeted_num` int(11) NOT NULL DEFAULT '0',
`rank` double NOT NULL DEFAULT '0',
`created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_date` datetime DEFAULT NULL,
PRIMARY KEY (`sid`),
KEY `sina_weibo_status_category_fk1` (`category`),
KEY `sina_weibo_status_rank_index` (`rank`),
KEY `sina_weibo_status_updated_date_index` (`updated_date`),
KEY `sina_weibo_status_uid` (`uid`),
KEY `sina_weibo_status_retweeted_status` (`retweeted_status`),
CONSTRAINT `sina_weibo_status_ibfk_1` FOREIGN KEY (`category`) REFERENCES `category` (`id`) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8查询语句:mysql> explain SELECT * FROM `sina_weibo_status` `t` WHERE ((category='11') AND (status='0')) AND (retweeted_status='0') ORDER BY rank desc LIMIT 20 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index_merge
possible_keys: sina_weibo_status_category_fk1,sina_weibo_status_retweeted_status
key: sina_weibo_status_category_fk1,sina_weibo_status_retweeted_status
key_len: 4,9
ref: NULL
rows: 275502
Extra: Using intersect(sina_weibo_status_category_fk1,sina_weibo_status_retweeted_status); Using where; Using filesort
排序没有使用索引,但是rank定义索引了,这是怎么回事?
`sid` bigint(20) NOT NULL DEFAULT '0',
`text` varchar(255) DEFAULT NULL,
`source` varchar(255) DEFAULT NULL,
`truncated` int(1) DEFAULT NULL ,
`thumbnail_pic` varchar(255) DEFAULT NULL,
`bmiddle_pic` varchar(255) DEFAULT NULL,
`original_pic` varchar(255) DEFAULT NULL,
`uid` bigint(20) DEFAULT NULL COMMENT,
`retweeted_status` bigint(20) DEFAULT '0',
`in_reply_to_status_id` bigint(20) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`aid` int(10) NOT NULL COMMENT,
`category` int(10) NOT NULL,
`status` tinyint(4) NOT NULL DEFAULT '0',
`retweeted_num` int(11) NOT NULL DEFAULT '0',
`rank` double NOT NULL DEFAULT '0',
`created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_date` datetime DEFAULT NULL,
PRIMARY KEY (`sid`),
KEY `sina_weibo_status_category_fk1` (`category`),
KEY `sina_weibo_status_rank_index` (`rank`),
KEY `sina_weibo_status_updated_date_index` (`updated_date`),
KEY `sina_weibo_status_uid` (`uid`),
KEY `sina_weibo_status_retweeted_status` (`retweeted_status`),
CONSTRAINT `sina_weibo_status_ibfk_1` FOREIGN KEY (`category`) REFERENCES `category` (`id`) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8查询语句:mysql> explain SELECT * FROM `sina_weibo_status` `t` WHERE ((category='11') AND (status='0')) AND (retweeted_status='0') ORDER BY rank desc LIMIT 20 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index_merge
possible_keys: sina_weibo_status_category_fk1,sina_weibo_status_retweeted_status
key: sina_weibo_status_category_fk1,sina_weibo_status_retweeted_status
key_len: 4,9
ref: NULL
rows: 275502
Extra: Using intersect(sina_weibo_status_category_fk1,sina_weibo_status_retweeted_status); Using where; Using filesort
排序没有使用索引,但是rank定义索引了,这是怎么回事?
解决方案 »
- mysql在Unix下不能完全读取数据??有些表可以读取有些表就出现错误提示~
- 疑问:MySQL存储过程
- WEB应用不同模块的数据放在一个数据库不同表中还是不同数据库中效率高?
- mysql 物理备份 xtrabackup 备份不了单个的数据库
- FORCE INDEX 无效,没有使用索引求解!
- 得分问题
- SELECT语句,不难的,请随便看看
- 去者有分
- mysql的临时文件突然增加几个G是什么情况?
- 请教 这个子查询怎么写,谢谢
- 如何才能把AUTO_INCREMENT列的值复制多一份存到其它的列
- Data truncation: Data too long for column 'url' at row 1
category、status、retweeted_status、rank上建立复合索引试试