MySQL “Limit” clause grabbing more results than requested

MySQL

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 <> 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     | <derived2> | 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?

Best Answer

If you only want the 100 rows (with offset 100), you don't need a subquery. Use what you name B, adding the username:

SELECT username, points
FROM users
WHERE points <> 0
ORDER BY points DESC
  LIMIT $start, $limit ;

If you have an index on (points, username), it will be pretty fast (at least when the offset is not huge).

The query you have is bringing more than 100 rows because, as @Phil pointed, you are using a subquery. You are joining table A (which probably has thousands or millions of rows) with the (derived) table B (which has 100 rows at maximum). But the joining is done using points which is not unique on the table. So, for some rows of table B, there are more than one rows from A joined.

Related Question