这是一个创建于 4289 天前的主题，其中的信息可能已经有所发展或是发生改变。
表结构：
CREATE TABLE `word` (
`id` int(8) ,
`word` int(8) ,
PRIMARY KEY (`id`,`word`)
);
主键为id+word（id并不是唯一），我要根据一个id,取出随机的一个word：
mysql> explain SELECT * FROM word where id=4963 ORDER BY rand() LIMIT 1;
+----+-------------+--------------+------+---------------+---------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+---------+---------+-------+------+----------------------------------------------+
| 1 | SIMPLE | word | ref | PRIMARY | PRIMARY | 3 | const | 12 | Using index; Using temporary; Using filesort |
+----+-------------+--------------+------+---------------+---------+---------+-------+------+----------------------------------------------+
显然使用 SELECT * FROM word where id=4963 ORDER BY rand() LIMIT 1; 性能很差
有什么办法可以优化这句SQL使之效率提高？
CREATE TABLE `word` (
`id` int(8) ,
`word` int(8) ,
PRIMARY KEY (`id`,`word`)
);
主键为id+word（id并不是唯一），我要根据一个id,取出随机的一个word：
mysql> explain SELECT * FROM word where id=4963 ORDER BY rand() LIMIT 1;
+----+-------------+--------------+------+---------------+---------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+---------+---------+-------+------+----------------------------------------------+
| 1 | SIMPLE | word | ref | PRIMARY | PRIMARY | 3 | const | 12 | Using index; Using temporary; Using filesort |
+----+-------------+--------------+------+---------------+---------+---------+-------+------+----------------------------------------------+
显然使用 SELECT * FROM word where id=4963 ORDER BY rand() LIMIT 1; 性能很差
有什么办法可以优化这句SQL使之效率提高？
 |
1
Livid MOD  1
只用 id 做主键，取出所有匹配的 word 之后，在编程语言这端来做随机。
|
 |
3
aveline 2013-02-18 04:17:35 +08:00 1
试试分两次... ?
先 SELECT COUNT(`word`) FROM `word` WHERE `id` = 4963 然后再 SELECT * FROM `word` WHERE `id` = 4963 LIMIT $RAND, 1 |
 |
4
0x0001 2013-02-18 06:58:39 +08:00
用编程语言得到随机id，然后根据这个id直接获取
SQL里的内置的随机，效率低下 |
Select Language