5

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)
  • Well, the OP can answer. What I think he means is that this query is slow and the other possible query (using the PK) would not work correctly (because of gaps in the pk). – ypercubeᵀᴹ Jan 5 '14 at 10:56
  • 1
    It's unclear what you mean by "Every nth row". The nth value would depend on the ordering of the rows in the table. – Thomas Kejser Jan 5 '14 at 11:46
  • 1
    Also, please elaborate on what you mean when you say "the table might have deleted rows". If they are deleted (as in, NOT there) - I assume you would not expect to sample them. – Thomas Kejser Jan 5 '14 at 11:48
  • 1
    You have to clarify what exactly you want by every n'th. If you'll be satisfied by 1/n subset despite of distribution within it - there is one solution. If you want subset evenly distributed among the complete data, like big strokes on the ruler - there is other solution, already mentioned by you. – Kondybas Jan 5 '14 at 14:29
  • @a_horse_with_no_name: I have clarified the question. Thank you for mentioning the concern. – dotancohen Jan 5 '14 at 15:04
4

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
  • Thank you Gord. Notice that the innermost query SELECT id FROM events ORDER BY id pulls from every row in the table, which I mention in the OP (and in the title) cannot be done on large databases. – dotancohen Jan 9 '14 at 7:28
  • @dotancohen This method does not "pull the entire table" in that it does not retrieve every row in its entirety. It only retrieves the primary key values for every row, which is necessary to determine the values for every n-th one if it must also "consider deleted rows" (a.k.a. "gaps in the sequence of PK values"). – Gord Thompson Jan 9 '14 at 9:29
  • You are right! I cautiously tested the query on a database with over 800,000 rows on every 100,000th row, and it ran in 0.72 seconds. Very nice! – dotancohen Jan 9 '14 at 9:36
1

To sample every n-th row randomly, I would use a hash function on the key.

For example, if you wanted ever 10th row, you could express your query like this:

SELECT foo, bar 
FROM MyTable 
WHERE CRC32(key) % 10 = 0

Assuming you pick a hash function (like CRC32) with a good spread, this should guard against holes in key that are there as a result of deletions.

Strictly speaking, this is not guaranteed to sample EXACTLY 1/nth of the table. But if the table is large enough that you care about the performance of this, I would assume that the solution gets you close enough.

It is important to realise that there is no such thing as a "row number" in a relational database. A table is simply an unordered set of tuples. The indexes on the table might store the tuples in a certain order, but that still does not add any form of row numbering to data. This means that a row number only has meaning in the presence of an ORDER BY clause.

In other words, if you mean that you want the Nth row with respect to some form of ordering of the table, then you have to express something with an ORDER BY in the SELECT. For example, this will do:

SELECT 
    @i:=@i+1 AS iterator 
    , t.foo
    , t.bar
FROM 
    MySql AS t,
    (SELECT @i:=0) AS dummy
WHERE @i % 10 = 0
ORDER BY somecolumn

If somecolumn is the primary index, this query should run without a sort or temp table. But you still have the visit every row to get the result.

  • Thank you Thomas. As you've stated, this provides a random sampling of 10% of the database. I usually do this with WHERE RAND() < %10 (which gives different rows each time). I'm looking for getting the first, n-th (n=2: second ; n=10: tenth) and each n-th row after that. – dotancohen Jan 5 '14 at 15:18
  • I see... As Kondybas then states, you must clarify what you mean by the Nth row. The ordering of the table will determine this - and in the presence of multiple indexes - there can be several right answers to what the Nth is – Thomas Kejser Jan 5 '14 at 15:34
  • @dotancohen: I have added clarifications in my answer based on the comments. – Thomas Kejser Jan 5 '14 at 16:49
  • Thank you, I've tried the suggested solution but it does not produce the expected results. Please see the edit in the OP. Thanks. – dotancohen Jan 8 '14 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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