MySQL – How to Select Every N-th Row Without Pulling Entire Table

MySQL

In light of the Meta discussion on allowing basic SQL questions on dba.SE I present an issue that I'm having now, for which the answer on Stack Overflow is inadequate and naive. I hope that there is a better solution to the issue than those presented on SO (as I am currently facing this issue in an application), and dba.SE seems to be the perfect place to find a better answer.

Here is the original question on Stack Overflow: How do you select every n-th row from mysql?

Here is the accepted answer:

SELECT * 
FROM ( 
    SELECT 
        @row := @row +1 AS rownum, [column name] 
    FROM ( 
        SELECT @row :=0) r, [table name] 
    ) ranked 
WHERE rownum % [n] = 1

The critical problem with the accepted answer is that it requires pulling the entire table into a temporary table. Thus, I've addressed that concern in the title of this question.

Consider also that the table might have deleted rows, thus an alternative query which were to simply test WHERE MOD on the primary key is not a good solution either. Id est, the primary key cannot be trusted to be sequential.

Is there a better way to phrase a query which would return every second, tenth, or arbitrary n-th row, which does not require pulling the entire table into memory yet also considers deleted rows?

Every n-th row can be defined as such:

n =  2: Rows 0, 2, 4, 6, 8, ...
n = 10: Rows 0, 10, 20, 30, ...
n = 42: Rows 0, 42, 84, 126, ...

My target DB is MySQL 5.5 running on a common Debian-derived Linux distro.

EDIT: In response to Thomas' answer:

The suggested solution does not produce the expected result, see below:

mysql> SELECT 
    ->     @i:=@i+1 AS iterator 
    ->     , t.name
    -> FROM 
    ->     events AS t,
    ->     (SELECT @i:=0) AS dummy
    -> WHERE @i % 10 = 0
    -> ORDER BY name ASC;
+----------+-------+
| iterator | name |
+----------+-------+
|        1 |     0 |
+----------+-------+
1 row in set (0.29 sec)

mysql> select count(*) from events;
+----------+
| count(*) |
+----------+
|   892507 |
+----------+
1 row in set (0.17 sec)

Best Answer

For test data in events

id  txtcol
--  ------
 1  event0
 2  event1
 4  event2
 5  event3
 6  event4
 8  event5
 9  event6

Retrieve the primary key values in ascending order

SELECT id FROM events ORDER BY id

Wrap that in a query to assign a zero-based rank

set @row:=-1;
SELECT @row:=@row+1 AS rownum, id 
FROM
    (
        SELECT id FROM events ORDER BY id
    ) AS sorted

Wrap that in a query to select the first row and every third row thereafter

set @row:=-1;
SELECT id
FROM
    (
        SELECT @row:=@row+1 AS rownum, id 
        FROM
            (
                SELECT id FROM events ORDER BY id
            ) AS sorted
    ) as ranked
WHERE rownum % 3 = 0

Finally, wrap that in a query to retrieve the other columns

set @row:=-1;
SELECT events.*
FROM
    events
    INNER JOIN
    (
        SELECT id
        FROM
            (
                SELECT @row:=@row+1 AS rownum, id 
                FROM
                    (
                        SELECT id FROM events ORDER BY id
                    ) AS sorted
            ) as ranked
        WHERE rownum % 3 = 0
    ) AS subset
        ON subset.id = events.id

returning

id  txtcol
--  ------
 1  event0
 5  event3
 9  event6
Related Question