MySQL "Limit" clause grabbing more results than requested I'm trying to add pagination to my points system (100 results per page) and came up with this :

SELECT A.username, A.points
FROM users A
INNER JOIN ( 
   SELECT points
   FROM users
   WHERE points &lt;&gt; 0
   ORDER BY points DESC
   LIMIT $start, $limit
)B
USING (points)


The username and points columns have indexes.

When running this query (LIMIT 100, 100), it returns this (in phpMyadmin) :

Showing rows 0 - 29 ( 124 total, Query took 0.0001 sec)


I'm asking for 100 rows, but it's returning 124. I've never seen this before.

Here is what EXPLAIN shows :

+----+-------------+------------+-------+---------------+--------+---------+----------+------+--------------------------+
| id | select_type | table      | type  | possible_keys | key    | key_len | ref      | rows | Extra                    |
+----+-------------+------------+-------+---------------+--------+---------+----------+------+--------------------------+
|  1 | PRIMARY     | &lt;derived2&gt; | ALL   | NULL          | NULL   | NULL    | NULL     |  100 |                          |
|  1 | PRIMARY     | A          | ref   | points        | points | 2       | B.points |    2 |                          |
|  2 | DERIVED     | users      | index | points        | points | 2       | NULL     |  200 | Using where; Using index |
+----+-------------+------------+-------+---------------+--------+---------+----------+------+--------------------------+


EXPLAIN shows 100 rows as well, but when outputing the array, it's still returning 124 results.

Any ideas how I can fix this?
This is happening because the LIMIT clause is in a subquery.

To fix it, move it to the end:

SELECT A.username, A.points
FROM users A
INNER JOIN ( 
   SELECT points
   FROM users
   WHERE points &lt;&gt; 0
   ORDER BY points DESC
)B
USING (points)
LIMIT $start, $limit


I suspect you'll also want to move the ORDER BY.