|
|
Posted by Bill Karwin on 10/09/71 11:31
Qun Cao wrote:
> select * from table order by rand() limit 1;
> returns one random entry from the table, exactly what i needed! :)
Be careful if your table contains a large number of rows.
Sorting by a function cannot benefit from any indexes defined on the
table. So the performance is as if you had no indexes, and for a large
table, this can cause bad performance to your PHP application.
You should try that command first and see how long it takes when applied
to your data (and also consider how much your table is expected to grow
in the future).
SELECT COUNT(*) FROM table does benefit from a unique index or primary
key on the table, but even that can be slow for a large table, or if the
index isn't cached.
You can maintain a table that notes the count of records, or you can use
SHOW TABLE STATUS to get an approximate count of records in a table.
See discussion of COUNT() in this page:
http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html
Regards,
Bill K.
Navigation:
[Reply to this message]
|