How To Fetch Random Row From MySQL Database Table Without Numeric ID?
database, mysql, perl, random
Solution
`LIMIT 1 OFFSET N` (where N is from 0 to number of records-1) would be better, less records transferred from DB server, and no need to generate random data for the sort.
Additionally, some RMDBs might perform that offset very efficiently - but I don't know if MySQL will on your table definition, you should test the performance.
Problem
I have a sharded MySQL database table spread across a number of servers, and want to have a function to fetch a random record. The way I see it, the best way to do this is: - Choose a random shard in the (Perl) application layer. - Choose a random row from the table on that shard. This question will focus on part 2 of the solution. My table does not have a numeric ID, we decided to use GUIDs in order to avoid the overhead of having to maintain a ticketing service. We combine our GUID with a shard identifier in order to ensure they are unique. Table is something like this: ``` guid name details 003-b0470602-a64b-11da-8632-93ebf1c0e05a Aura Some details ``` The best answer I've come up with so far was suggested by Jack, which is to: - Count the number of records in the database. - Get a random number between 1 and the result from step 1. - `SELECT * FROM table LIMIT {result from step 2}` - Take the last result from this (potentially very large) result set. I'm worried that the processing involved in this could be quite large (though certainly better than `ORDER BY RAND() LIMIT 1`, and wondering if anyone has any better solutions which work with a non numeric identifier.