… ORDER BY RAND()

Goal: get random record from database

Lets sat we have table `lyric` ~ 33k records, `id` is primary key

Simple:
SELECT * FROM `lyric` ORDER BY RAND() LIMIT 1;

1 row in set (1.12 sec)

wtf!? 1 second ?

Note: MyISAM keep cache for meta-data (e.g. total row count). We use InnoDB

mysql> EXPLAIN SELECT * FROM `lyric` ORDER BY RAND() LIMIT 1;
+—-+————-+——-+——+—————+——+———+——+——-+———————————+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra                           |
+—-+————-+——-+——+—————+——+———+——+——-+———————————+
|  1 | SIMPLE      | lyric | ALL  | NULL          | NULL | NULL    | NULL | 26909 | Using temporary; Using filesort |
+—-+————-+——-+——+—————+——+———+——+——-+———————————+
1 row in set (0.00 sec)

again .. wtf!?

My idea:

mysql> SELECT * FROM `lyric` WHERE `id` > 4000 LIMIT 1;
…..
1 row in set (0.00 sec)

4000 is just for example, we will use PHP to generate this number.

0.00 seconds is much much better than 1.xx

mysql> EXPLAIN SELECT * FROM `lyric` WHERE `id` > 4000 LIMIT 1;
+—-+————-+——-+——-+—————+———+———+——+——-+————-+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
+—-+————-+——-+——-+—————+———+———+——+——-+————-+
|  1 | SIMPLE      | lyric | range | PRIMARY       | PRIMARY | 4       | NULL | 13454 | Using where |
+—-+————-+——-+——-+—————+———+———+——+——-+————-+
1 row in set (0.00 sec)

My idea is to keep cache of MAX(`id`) and use something like

WHERE `id` > <?php echo rand ( 0 , $cachedMaxID ); ?>

But this a bad option if we have a lot of missing(deleted) primary keys. If we have pk from 0-100 but 100-200 are missing. Change to get 201 is ~50%

php rand()

MySQL RAND()

This entry was posted in MySQL, Optimization, Optimization, php. Bookmark the permalink.