MySQL中如何使用索引优化ORDER BY
0
悬赏园豆：20
[已关闭问题]
关闭于 2015-09-18 10:09
CREATE TABLE IF NOT EXISTS `uchome_plug_pai_photo` ( `id` int(11) NOT NULL, ............... `wappic` varchar(200) NOT NULL, `bigpic` varchar(60) NOT NULL, `picwidth` int(4) NOT NULL, `picheight` int(4) NOT NULL, `province` varchar(64) NOT NULL, `city` varchar(64) NOT NULL, `county` varchar(50) NOT NULL, `place` varchar(50) NOT NULL, `privacyplace` tinyint(1) NOT NULL DEFAULT '0', `steps` int(11) NOT NULL, `athome` tinyint(1) NOT NULL DEFAULT '1', .................... `dateline` int(10) NOT NULL, `reconum` int(11) NOT NULL DEFAULT '0', `collnum` int(11) NOT NULL, `viewnum` int(11) NOT NULL DEFAULT '0', `replynum` int(11) NOT NULL DEFAULT '0', `likenum` int(11) NOT NULL DEFAULT '0', `state` tinyint(1) NOT NULL DEFAULT '0', .................. `mpic` text, `stick` tinyint(4) NOT NULL DEFAULT '0', `commenttime` datetime NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
表结构大致如上,现在有这样一个查询
1 select * from uchome_plug_pai_photo where state>0 ORDER BY stick desc,dateline DESC LIMIT 47701,10
请问我该如何创建复合索引,为什么?
我创建了一个,
1 alter table uchome_plug_pai_photo add index sd(stick,dateline);
执行结果如下,
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | uchome_plug_pai_photo | index | suli | sd | 5 | NULL | 50389 | Using where |
但是总感觉不好,还请大神们帮忙看看.
