表结构: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定义索引了,这是怎么回事?
解决方案 »
- java 开发 mysql,无法释放连接
- mysql大概一万条怎么数据导出啊,有快速的方法吗
- mysql修复数据库的问题(急)
- mysql grant 多个ip
- 不显示删除回复显示所有回复显示星级回复显示得分回复 请问字段1和字段2内容合并生成新字段3,mysql语句怎么写[问题点数:40分]
- 求助,谁能帮帮我啊,在线等!
- 关于mysql数据库的同步
- win2000下的mysql服务器启动问题
- 分布式存储,联合检索
- 特殊字符串,如何写入 数据里?
- 如何才能把AUTO_INCREMENT列的值复制多一份存到其它的列
- Data truncation: Data too long for column 'url' at row 1
category、status、retweeted_status、rank上建立复合索引试试