Hi~
I have inserted 1,000,000 record into table.
and selected 1 record using order by rand().
but, it was very very slow.
mysql> explain member ;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| num | int(11) | | PRI | NULL | auto_increment |
| id | varchar(20) | YES | MUL | NULL | |
| name | varchar(20) | YES | MUL | NULL | |
| address | varchar(50) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| point | int(11) | YES | | NULL | |
| introduce | varchar(255) | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
mysql> select count(*) from member ;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.00 sec)
mysql> select id, name from member order by rand() limit 1 ;
+----------------------+----------------------+
| id | name |
+----------------------+----------------------+
| CCDJUBUSRMUSKBDTMNPW | XMQPSKIMKFCWGPNDSGRD |
+----------------------+----------------------+
1 row in set (2 min 42.14 sec)
( I inserted column values randomly selected alphabet)
mysql> select id, name from member limit 12313, 1;
+----------------------+----------------------+
| id | name |
+----------------------+----------------------+
| FAICNUXHARYCCLVMIDDS | UYQEGOTCYPRRPZIYRDYK |
+----------------------+----------------------+
1 row in set (1.35 sec)
mysql> select version() ;
+-----------+
| version() |
+-----------+
| 3.23.32 |
+-----------+
1 row in set (0.00 sec)
[wertyu@inos ~]$ uname -a
Linux 2.2.5-22 #1 Thu Nov 23 18:33:46 KST 2000 i686 unknown
Hardware : Pentium II 233Mhz with 128Mb Memory
My suggestion is if somebody uses order by rand(), it's better to change to sel
ect count() record, and generate random number between 1 and row count, and ret
rieve with LIMIT rand_num, 1
Am I correct?
Thank you~
--MIME Multi-part separator--
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
