How To Improve Limit Clause In Mysql

I have the posts table with 10k rows and I want to create pagination by that. So I have the next query for that purpose: SELECT post_id FROM posts LIMIT 0, 10; When I Expl

Solution 1:

Try this:

SELECT post_id
    FROM posts
    ORDERBY post_id DESC
    LIMIT 0, 10;

Pagination via LIMIT doesn't make much sense without ordering anyway, and it should fix your problem.

mysql> explain select*from foo;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| id | select_type |table| type  | possible_keys | key     | key_len |ref|rows| Extra       |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+|1| SIMPLE      | foo   | index |NULL|PRIMARY|4|NULL|20|Using index |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+1rowinset (0.00 sec)

mysql> explain select*from foo limit 0, 10;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| id | select_type |table| type  | possible_keys | key     | key_len |ref|rows| Extra       |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+|1| SIMPLE      | foo   | index |NULL|PRIMARY|4|NULL|20|Using index |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+1rowinset (0.00 sec)

mysql> explain select*from foo orderby id desc limit 0, 10;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| id | select_type |table| type  | possible_keys | key     | key_len |ref|rows| Extra       |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+|1| SIMPLE      | foo   | index |NULL|PRIMARY|4|NULL|10|Using index |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+1rowinset (0.00 sec)

Regarding your last comments about the comment join. Do you have an index on comment(post_id)? with my test data I'm getting the following results:

mysql>altertable comments add index pi (post_id);
Query OK, 0rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select c.id from  comments c innerjoin (select id from posts o orderby id  limit 0, 10) p on c.post_id = p.id;
+----+-------------+------------+-------+---------------+---------+---------+------+------+--------------------------+| id | select_type |table| type  | possible_keys | key     | key_len |ref|rows| Extra                    |+----+-------------+------------+-------+---------------+---------+---------+------+------+--------------------------+|1|PRIMARY|<derived2>|ALL|NULL|NULL|NULL|NULL|10|||1|PRIMARY| c          |ref| pi            | pi      |5| p.id |4|Usingwhere; Using index ||2| DERIVED     | o          | index |NULL|PRIMARY|4|NULL|10|Using index              |+----+-------------+------------+-------+---------------+---------+---------+------+------+--------------------------+

and for table size reference:

mysql>selectcount(*) from posts;
+----------+|count(*) |+----------+|15021|+----------+1rowinset (0.01 sec)

mysql>selectcount(*) from comments;
+----------+|count(*) |+----------+|1000|+----------+1rowinset (0.00 sec)