[valdet]

Sorry to bump this thread.

Originally Posted by noppid

That query will/could bring a forum to it's knees running every page view.

mysql> explain Select * From thread order by rand() DESC LIMIT 1
    -> ;
+----+-------------+--------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+--------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | thread | ALL  | NULL          | NULL | NULL    | NULL | 6026 | Using temporary; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.03 sec)

As you can see, a full table walk.

Here's a better way.

60*60 == 3600
3600*24 == 14400
14400*30 == 432000
1223479602 - 432000 == 1223047602

mysql> explain Select * From thread where dateline >= 1223047602 order by dateline DESC limit 1;
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | thread | range | dateline      | dateline | 4       | NULL |  104 | Using where |
+----+-------------+--------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)

See the difference? Gotta use those indexes. Rand() is bad.

Can you please let me know how would I optimize this query so it doesn't overload the database as much as with rand() function.

Also how to add the limitations so it does not opens threads from private forums?

Thanks