MySQL - select N random rows

In this article, we would like to show you how to select N random rows from a table in MySQL.

Quick solution:

SELECT *
FROM `table_name`
ORDER BY RAND()
LIMIT 3;

Where: 3 means number of random values.

Hint: quick solution has weak performance.

Optimal solutions

1. OFFSET + LIMIT based solutons

It is necessary to query multiple times database using LIMIT random_row_index, 1 clause in query. In MySQL syntax it is not possible to use expression inside LIMIT clause so the solution is to generate queries and execute them later.

Example solution using JavaScript:

// ONLINE-RUNNER:browser;

const randomInteger = (max) => {
    return Math.floor(max * Math.random());
};

const randomOffsets = (randomCount, totalCount) => {
    if (0.5 * totalCount > randomCount) {  // 0.5 was selected empirically
        const offsets = new Set();
        for (let i = 0; i < randomCount;) {
            const offset = randomInteger(totalCount);
            if (offsets.size !== (offsets.add(offset), offsets.size)) {
                i += 1;
            }
        }
        return Array.from(offsets);
    } else {
        const offsets = new Array(totalCount);
        for (let i = 0; i < totalCount; ++i) {
            offsets[i] = i;
        }
        for (let i = offsets.length - 1; i > 0; --i) {
            const j = randomInteger(i + 1);
            const tmp = offsets[i];
            offsets[i] = offsets[j];
            offsets[j] = tmp;
        }
        if (totalCount > randomCount) {
            return offsets.slice(0, randomCount);
        }
        return offsets;
    }
};


// Usage example:

const totalCount = 100;
const randomCount = 3;

const offsets = randomOffsets(randomCount, totalCount);

for (const offset of offsets) {
    console.log(`SELECT * FROM \`table_name\` LIMIT ${offset}, 1;`);
}

2. WHERE + LIMIT 1 based solutons

This solution is similar to the above one. It gives much more better performance but can be used only when primary keys are generated continuously and data are ordered by primary keys. That means, by using this solution we can not remove primary keys.

Example solution using JavaScript:

// ONLINE-RUNNER:browser;

const randomInteger = (max) => {
    return Math.floor(max * Math.random());
};

const randomOffsets = (randomCount, totalCount) => {
    if (0.5 * totalCount > randomCount) {  // 0.5 was selected empirically
        const offsets = new Set();
        for (let i = 0; i < randomCount;) {
            const offset = randomInteger(totalCount);
            if (offsets.size !== (offsets.add(offset), offsets.size)) {
                i += 1;
            }
        }
        return Array.from(offsets);
    } else {
        const offsets = new Array(totalCount);
        for (let i = 0; i < totalCount; ++i) {
            offsets[i] = i;
        }
        for (let i = offsets.length - 1; i > 0; --i) {
            const j = randomInteger(i + 1);
            const tmp = offsets[i];
            offsets[i] = offsets[j];
            offsets[j] = tmp;
        }
        if (totalCount > randomCount) {
            return offsets.slice(0, randomCount);
        }
        return offsets;
    }
};


// Usage example:

const totalCount = 100;
const randomCount = 3;

const offsets = randomOffsets(randomCount, totalCount);

for (const offset of offsets) {
    console.log(`SELECT * FROM \`table_name\` WHILE \`id\` > ${offset} LIMIT 1;`);
}

Practical example

Use the bellow database to check how it works, changing number of random values.

SELECT *
FROM `messages`
ORDER BY RAND()
LIMIT 3;

Example result:

Database preparation

create_tables.sql file:

CREATE TABLE `messages` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `text` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

insert_data.sql file:

INSERT INTO `messages`
    (`text`)
VALUES
    ('Message 1'),
    ('Message 2'),
    ('Message 3'),
    ('Message 4'),
    ('Message 5'),
    ('Message 6'),
    ('Message 7'),
    ('Message 8'),
    ('Message 9'),
    ('Message 10');