Self join increase paging query performance I saw a question on stackoverflow 
mysql self join performance. The question mentioning that self join will increase query performance. For example this query 

select m2.* from message m1,message m2
where m1.id=m2.id and m1.thumbs_up &lt;=98
and (m1.id&lt;13 or m1.thumbs_up&lt;98)
order by m1.thumbs_up desc,m1.id desc


explain result 

+----+-------------+-------+--------+-----------------------+---------------+---------+------------+-------+--------------------------+
| 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                     |
+----+-------------+-------+--------+-----------------------+---------------+---------+------------+-------+--------------------------+


have better performance than 

select *
from message
where thumbs_up &lt;=98 and (id&lt;13 or thumbs_up&lt;98)
order by thumbs_up desc,id desc


explain result

+----+-------------+---------+------+-----------------------+------+---------+------+-------+-----------------------------+
| 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 |
+----+-------------+---------+------+-----------------------+------+---------+------+-------+-----------------------------+


I tried the query using profiling and yes the performance is actually better specially in create sort index. Is it possible to explain why as I found it interesting thing to know (I tried using explain query but didn't find good explanation).   

update: 

create table query 

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`)
)


Also I removed all indexes except the primary key index, the performance difference was huge the like double the speed.
I really cannot see the point of using self-join in this query. It is 100% equivalent to this:

select m.* 
from message m
where m.thumbs_up = 98 and m.id &lt; 13 
   or m.thumbs_up &lt; 98
order by m.thumbs_up desc, m.id desc 
-- limit N 
  ;


I would expect an index on (thumbs_up, id) to be used by this.