select a random record from a table, why is first slowly than second?

Question

I want to select a random record from a big table. After search, found two solutions finally;

a:

select id  from `table` where id = (floor(1 + rand() * 2880000));

b:

select id  from `table` where id >= (floor(1 + rand() * 2880000)) limit 1;

But first(a) solution is far slowly than Second(b), about 40 times slowly.

After execute many times, I find a more weird problem. First solution may return two record.

select id  from `table` where id = (floor(1 + rand() *  2880000));
+---------+
| id      |
+---------+
| 2484024 |
| 1425029 |
+---------+
2 rows in set (1.06 sec)

My question is:

• Why is first solution slowly than second solution?
• Why did first solution return two record?

My MySQL version:

mysql> show variables like "%version%";
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.5.43                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| version                 | 5.5.43-0ubuntu0.12.04.1 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | debian-linux-gnu        |
+-------------------------+-------------------------+
7 rows in set (0.04 sec)

Thanks for any help.

Answer 1

The answers to both of your questions:

1. The first solution is slower than the second because in the first solution a new random value is calculated for every record, while in the second solution it's calculated only for the records needed to find one match. Also note that the condition for the second solution is much less strict.
2. You can have multiple return values in the first solution because a new random value is calculated for every record, and you don't have a limit statement. By the same logic, you could also have 0 results.

Take a look at this answer for a better solution.