Select random line in SQL database

Question

I would like to select a random line in my database. I saw this solution on a website:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

This SQL query run but someone said me that it was a non performant query. Is there another solution ?

Thx

For future reference: RAND() only works on mysql. – Maerlyn May 27 '10 at 05:09 — Maerlyn, May 27 '10 at 05:09

score 3 · Answer 1 · answered May 27 '10 at 03:17

3

It is. You have to count rows number with

SELECT COUNT(*) FROM `table`;

After this with php function mt_rand() get the random from 1 to $count and get it with query:

SELECT `column` FROM `table` LIMIT $rand, 1

answered May 27 '10 at 03:17

zerkms

Ok, Thanks :) Another question, unrelated development, I'm French and I would like to know if my English is correct, comprehensible ? – Jensen May 27 '10 at 03:24
oops, in some reason i thought you asked about `mysql`. Did you? :-S – zerkms May 27 '10 at 03:28
For english ? No, not about mysql. – Jensen May 27 '10 at 03:33
Then what dbms did you talk about at the initial question? `Microsoft SQL Server`? – zerkms May 27 '10 at 03:34
Ohh the initial question, yes there was MySQL ^^ – Jensen May 27 '10 at 03:37
@Jensen: I´m Argentinian and speak Spanish, but understand your English perfectly. – Claudia May 27 '10 at 13:18

score 0 · Answer 2 · edited May 23 '17 at 12:18

0

There's a lot more discussion of this subject, including performance implications and strategies for different DBMSs, in this question.

edited May 23 '17 at 12:18

Community

answered May 27 '10 at 03:34

Ken Redler

All the answers from that topic can be separated into 2 categories. 1. **slow** with using of RAND() (or DBMS-specific synonym) or 2. the calculating some random `id` in the range [1, id] and finding value that >= of that. and this solution gives us irregular randomize coverage (Ex: 1, 2, 3, 4, 5, 6, 7, 8, 9, 1000000000000. In this case in 99.99% of cases you will get the latest row). This is **not true uniform random** – zerkms May 27 '10 at 03:40
I think this is a matter of TANSTAAFL. Approaches like TABLESAMPLE clearly trade randomness for speed (and are vendor-specific). If you want to go down the rabbit-hole of seeking true randomness, you're adding a theoretical math problem to an implementation challenge. You could add a trigger that populates a column with a pseudo-random number on insert, "pre-slicing" your large table into manageable shards, then select a row, rand() limit 1, from one shard. So, pre-calculating some of the "fake" randomness. I agree that all these approaches involve compromise between purity and practicality. – Ken Redler May 27 '10 at 04:06

2 Answers2