mysql自关联分页_mysql – 自联接增加分页查询性能

我在stackoverflow上看到了一个问题

mysql self join performance.提到自联接的问题将提高查询性能.例如这个查询

select m2.* from message m1,message m2

where m1.id=m2.id and m1.thumbs_up <=98

and (m1.id<13 or m1.thumbs_up<98)

order by m1.thumbs_up desc,m1.id desc

解释结果

+----+-------------+-------+--------+-----------------------+---------------+---------+------------+-------+--------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+--------+-----------------------+---------------+---------+------------+-------+--------------------------+

| 1 | SIMPLE | m1 | range | PRIMARY,thumbs_up_key | thumbs_up_key | 4 | NULL | 21574 | Using where; Using index |

| 1 | SIMPLE | m2 | eq_ref | PRIMARY | PRIMARY | 4 | test.m1.id | 1 | NULL |

+----+-------------+-------+--------+-----------------------+---------------+---------+------------+-------+--------------------------+

有比…更好的表现

select *

from message

where thumbs_up <=98 and (id<13 or thumbs_up<98)

order by thumbs_up desc,id desc

解释结果

+----+-------------+---------+------+-----------------------+------+---------+------+-------+-----------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+---------+------+-----------------------+------+---------+------+-------+-----------------------------+

| 1 | SIMPLE | message | ALL | PRIMARY,thumbs_up_key | NULL | NULL | NULL | 43148 | Using where; Using filesort |

+----+-------------+---------+------+-----------------------+------+---------+------+-------+-----------------------------+

我尝试使用性能分析进行查询,是的,在创建排序索引时,性能实际上更好.是否有可能解释为什么我发现有趣的事情要知道(我尝试使用解释查询,但没有找到好的解释).

更新：

创建表查询

CREATE TABLE `message`(

`id` int(11) NOT NULL AUTO_INCREMENT,

`title` varchar(255) NOT NULL,

`user_id` int(11) NOT NULL,

`content` text NOT NULL,

`create_time` int(11) NOT NULL,

`thumbs_up` int(11) NOT NULL DEFAULT '0', /*Vote Count*/

PRIMARY KEY (`id`),

KEY `thumbs_up_key` (`thumbs_up`,`id`)

)

另外我删除了除主键索引之外的所有索引,性能差异巨大,就像速度加倍一样.