关于order by和limit的问题

[kuangzhc]

表结构
| v9_news | CREATE TABLE `v9_news` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`catid` smallint(5) unsigned NOT NULL DEFAULT '0',
`typeid` smallint(5) unsigned NOT NULL,
`title` varchar(80) NOT NULL DEFAULT '',
`style` char(24) NOT NULL DEFAULT '',
`thumb` varchar(255) NOT NULL DEFAULT '',
`keywords` varchar(255) NOT NULL DEFAULT '',
`description` mediumtext NOT NULL,
`posids` tinyint(3) unsigned NOT NULL DEFAULT '0',
`url` char(100) NOT NULL,
`listorder` tinyint(3) unsigned NOT NULL DEFAULT '0',
`status` varchar(2) NOT NULL DEFAULT '',
`sysadd` tinyint(1) unsigned NOT NULL DEFAULT '0',
`islink` tinyint(1) unsigned NOT NULL DEFAULT '0',
`username` char(20) NOT NULL,
`inputtime` int(10) unsigned NOT NULL DEFAULT '0',
`updatetime` int(10) unsigned NOT NULL DEFAULT '0',
`shorttitle` varchar(255) NOT NULL DEFAULT '',
`list_description` mediumtext NOT NULL,
PRIMARY KEY (`id`),
KEY `inputtime` (`inputtime`),
KEY `catid` (`catid`,`inputtime`),
KEY `keywords` (`catid`,`keywords`),
KEY `title` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=1997500 DEFAULT CHARSET=gbk

表索引：
--------+---------+
| | 0 | PRIMARY | 1 | id | A | 906171 | NULL | NULL | | BTREE | |
| | 1 | inputtime | 1 | inputtime | A | 906171 | NULL | NULL | | BTREE | |
| | 1 | catid | 1 | catid | A | 347 | NULL | NULL | | BTREE | |
| | 1 | catid | 2 | inputtime | A | 906171 | NULL | NULL | | BTREE | |
| | 1 | keywords | 1 | catid | A | 347 | NULL | NULL | | BTREE | |
| | 1 | keywords | 2 | keywords | A | 453085 | NULL | NULL | | BTREE | |
| | 1 | title | 1 | title | A | 906171 | NULL | NULL | | BTREE | |
+---------+------------+-----------+--------------+-------------+-----------+-------------+------
问题：看两条语句的执行结果以及语句
1、select title,url from pinpai_wfnews.v9_news where catid='1642' and username='duoduo' order by id asc limit 1;
Empty set (2.13 sec)
2、select title,url from pinpai_wfnews.v9_news where catid='1642' and username='duoduo' order by id asc ;
Empty set (0.08 sec)
3、select title,url from pinpai_wfnews.v9_news where catid='1642' and username='duoduo' limit 1;
Empty set (0.11 sec)
4、select title,url from pinpai_wfnews.v9_news where catid='1642' and username='duoduo' order by catid asc limit 1;
Empty set (0.08 sec)
为什么前两个的结果相差那么大？
第4个更换排序字段后，速度明显变快，该字段有索引。
然后在另一库中的同样一张表中，执行第一条查询语句结果相差那么大 这是为什么？
select title,url from dzwww.v9_news where catid='1642' and username='duoduo' order by id asc limit 1;
Empty set (0.00 sec)

[rucypli]

看执行计划啊

[ACMAIN_CHM]

第一条语句到底是 2.13 sec 还是 时间起伏在0.10-0.40左右 建议贴出的所有测试结果。

[tashiwoweiyi]

说实话不知道你如何测试的。 这应该跟limit没太多的关系，第一条和第二语句不可能执行时间相差这么大。

[ACMAIN_CHM]

建议楼主 每条语句先多做几次，看一下平均结果。

[kuangzhc]

第一条语句做了不下十次了，基本上语句的时间起伏在0.10-0.40左右。

[Barton]

看执行计划~~

[kuangzhc]

引用 12 楼 ACMAIN_CHM 的回复:

认真对比一下楼主不难发现差异所在。 Using where; Using filesort |

两个表条件相同，但是执行计划的结果都不一样，问题表的 PRIMARY | 3 | NULL | 51 | Using where 第二个表的 keywords | 2 | const | 1 | Using where; Using filesort | 语句相同，难道还有其他条件限制了？

[kuangzhc]

引用 13 楼 kuangzhc 的回复:

[quote=引用 12 楼 ACMAIN_CHM 的回复:] 认真对比一下楼主不难发现差异所在。 Using where; Using filesort |

两个表条件相同，但是执行计划的结果都不一样，问题表的 PRIMARY | 3 | NULL | 51 | Using where 第二个表的 keywords | 2 | const | 1 | Using where; Using filesort | 语句相同，难道还有其他条件限制了？[/quote] 问题库的数据量90W左右 第二库的数据量为206W左右

[kuangzhc]

explain select title,url from pinpai_wfnews.v9_news where catid='1642' and username='duoduo' order by id asc limit 1; +----+-------------+---------+-------+----------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+----------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | v9_news | index | catid,keywords | PRIMARY | 3 | NULL | 51 | Using where | +----+-------------+---------+-------+----------------+---------+---------+------+------+-------------+

[Barton]

引用 7 楼 kuangzhc 的回复:

explain select title,url from pinpai_wfnews.v9_news where catid='1642' and username='duoduo' order by id asc limit 1; +----+-------------+---------+-------+----------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+----------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | v9_news | index | catid,keywords | PRIMARY | 3 | NULL | 51 | Using where | +----+-------------+---------+-------+----------------+---------+---------+------+------+-------------+

你这个SQL是通过走聚集索引来查询数据，然后再利用where条件，所以会比较慢，贴下你第二个SQl的执行计划看看

[kuangzhc]

+----+-------------+---------+------+----------------+----------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+----------------+----------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | v9_news | ref | catid,keywords | keywords | 2 | const | 1 | Using where; Using filesort | +----+-------------+---------+------+----------------+----------+---------+-------+------+-----------------------------+ 这个是第二条语句的 explain select title,url from dzwww.v9_news where catid='1642' and username='duoduo' order by id asc;

[kuangzhc]

explain select title,url from dzwww.v9_news where catid='1642' and username='duoduo' limit 1; +----+-------------+---------+------+----------------+-------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+----------------+-------+---------+-------+------+-------------+ | 1 | SIMPLE | v9_news | ref | catid,keywords | catid | 2 | const | 1 | Using where | +----+-------------+---------+------+----------------+-------+---------+-------+------+-------------+

[kuangzhc]

explain select title,url from dzwww.v9_news where catid='1642' and username='duoduo' order by id asc limit 1; +----+-------------+---------+------+----------------+----------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+----------------+----------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | v9_news | ref | catid,keywords | keywords | 2 | const | 1 | Using where; Using filesort | +----+-------------+---------+------+----------------+----------+---------+-------+------+-----------------------------+ 这个是第二个库中相同表的执行计划

[ACMAIN_CHM]

认真对比一下楼主不难发现差异所在。 Using where; Using filesort |

[kuangzhc]

求教