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()