Baron Schwartz's Website
Archives Talks

MySQL Challenge: LIMIT Rows Accessed, Not Rows Returned

Published Jun 28, 2008 by Baron Schwartz in Databases, Programming at https://www.xaprb.com/blog/2008/06/28/mysql-challenge-limit-rows-accessed-not-rows-returned/

Dear reader, this is a challenge. How’s your MySQL prowess? You know about LIMIT: it cuts off the results at the specified number.

mysql> select actor_id from sakila.actor where actor_id % 5 = 0 limit 5;
+----------+
| actor_id |
+----------+
|        5 | 
|       10 | 
|       15 | 
|       20 | 
|       25 | 
+----------+
5 rows in set (0.00 sec)

But that query actually accessed 25 rows. What if I want to say “return up to 5 rows, but don’t read any more than 20 rows to find them?”

Right now I’ve got the following:

mysql> select actor_id, @rows
    -> from actor, (select @rows := 0) as x where
    ->    ((@rows := @rows + 1) <= 20)
    ->    and actor_id % 5 = 0 
    -> limit 5;
+----------+-------+
| actor_id | @rows |
+----------+-------+
|        5 | 5     | 
|       10 | 10    | 
|       15 | 15    | 
|       20 | 20    | 
+----------+-------+
4 rows in set (0.00 sec)

The derived table subquery x is only there to initialize the user variable at the beginning of the query.

This appears to work, but it doesn’t. If you profile this with SHOW STATUS, you see that it reads every row in the table (Handler_read_next = 200). This is actually worse, not better, than just LIMIT.

Any ideas?

I’ve got a few. But I don’t like them for various reasons. Extra props for really efficient solutions that don’t involve subqueries (so it’ll work on pre-4.0) or things that add extra overhead (subqueries, for example). I guess you probably see the direction I want to go with this—I don’t want to use subqueries.

Baron Schwartz

I’m a founder, author of several books, and creator of various open-source software. I write about topics such as technology, mindfulness, and fitness, and I tweet at @xaprb. More about me.

Story logo

© 2020 Baron Schwartz