How To Fetch Random Row From MySQL Database Table Without Numeric ID?

Question

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:

1. Choose a random shard in the (Perl) application layer.
2. 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:

1. Count the number of records in the database.
2. Get a random number between 1 and the result from step 1.
3. SELECT * FROM table LIMIT {result from step 2}
4. 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.

Answer 1

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.