有没有更好的方法来获得限制1的随机记录

Is there a better way to get a random record with limit 1

I am using the below Query

$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");

but this creates a terrible bottleneck to me (my system slows down very much almost it gets hanged). Is there a better option to do this.

I have tried this which has given me a better performance

$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);

$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

You are right, sorting a table randomly can be extremely slow.

A faster solution would be to do something like:

SELECT * FROM user WHERE id >= (SELECT FLOOR(MAX(id) * RAND()) FROM user) ORDER BY id LIMIT 1;

The drawback from this approach is that the result is not necessarily well balanced based on the data if the ids are not evenly distributed.

This should do this work:

SELECT username FROM user where ID =
  (SELECT ID FROM user ORDER BY RAND() LIMIT 1)

Go small deeper.

@JuliePelletier's solution is too good, but there are things to fix.

SELECT * FROM user WHERE id >= (SELECT FLOOR(MAX(id) * RAND()) FROM user) ORDER BY id LIMIT 1;  

• Need to replace FLOOR to CEIL to possibility to select last user. And even remove it at all, because we use >= here.
• MAX(id) is amazing optimized call(N(1)), but if we multiplicate it with RAND(), it lost optimization. SELECT CEIL(RAND() * (SELECT MAX(ID))) fixes it.
• WHERE id>= X executes for each row while we looking for correct user, so relative heavy rand and ceil functions will call multiple times. It relates to my query too.

We need to find some query to be sure we execute rand() only once.
Let's try:

SELECT username
FROM users AS u1
  JOIN (SELECT (RAND() * (SELECT MAX(id) FROM random)) AS id) u2
WHERE u1.id >= u2.id
ORDER BY u1.id ASC
LIMIT 1;

This query is optimized but has unbalanced results.
Example:

• We have followed ID's: 1, 100 in our table
• Query will return user#100 in 99 cases of 100.

To solve this issue we can run ID's normalization while maintenance or create other table with mapping sequenced number with existed user IDs.

Last solution works well if you remove your users rarely:

create table user_map (
  id int not NULL primary key,
  user_id int not null
);
SET @i = 0;
INSERT INTO user_map SELECT @i := @i + 1, ID FROM users;

Now we have sequenced ids, and can to select balanced random user:

SELECT username FROM users u
JOIN (
    SELECT r1.user_id FROM user_map AS r1
    JOIN (
        SELECT (RAND() * (SELECT MAX(row_id) FROM user_map)) AS id
    ) AS r2
    WHERE r1.id >= r2.row_id
    ORDER BY r1.row_id ASC
    LIMIT 1
) as rows ON (u.id = rows.user_id);

All we need now is to add new records to user_map when new user appeared and (heavy task) rebuild it when user deleted. It can be setted up with triggers.

• ORDER BY RAND() is O(Nlog(N)) [with small constant if just index scanned, but still nlogn]
• last method is near O(1)

Try this,

    SELECT 
        username 
    FROM 
        user 
    WHERE
        id = ( 
              SELECT ROUND((RAND() * ( MAX(id)-MIN(id) ))+ MIN(id)) 
              )    ; 

< SELECT ROUND((RAND() * ( MAX(id)-MIN(id) ))+ MIN(id)) > would give you random ID from the table between MAX(id) and MIN(id).

I presume, you have < id > field, with index on it.

You can use random in WHERE clause. It will be much faster than in ORDER BY. Like this:

SELECT username
FROM user
WHERE id >= RAND() * (SELECT MAX(id) FROM user)
ORDER BY id
LIMIT 1