Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Which rows do you want to get? Why do you need a subquery? –  ypercube Dec 10 '12 at 6:13
    
I'm trying to get the retrieve the usernames along with their points. Without a subquery, my query was scanning all 5000 rows just to get the 100 results that were needed. As you can see above, it only needed to scan 200 rows to get the results I'm after. –  Dave Dec 10 '12 at 6:22
add comment

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
Ah .. I had indexes on [username, points] (reverse order) - switching them around solved the issue. Thank you. –  Dave Dec 10 '12 at 6:33
add comment

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 <> 0
   ORDER BY points DESC
)B
USING (points)
LIMIT $start, $limit

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

share|improve this answer
    
Moving one (or both lines) causes the query to return all rows. –  Dave Dec 10 '12 at 6:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.