I have query that gets 10 random posts , and as you know this is very slow and heavy query, is there any alternatives to submit this query without any slow appearance?
my current rand query :
SELECT * FROM posts ORDER BY RAND() LIMIT 10
Asked
Active
Viewed 1,013 times
0
-
Can you cache it or does it need to be random on every page load? – Treffynnon Jan 11 '12 at 14:13
-
possible duplicate of [MySQL: Alternatives to ORDER BY RAND()](http://stackoverflow.com/questions/1823306/mysql-alternatives-to-order-by-rand) – Treffynnon Jan 11 '12 at 14:16
-
@Treffynnon yeah it needs to be random every time – homerun Jan 11 '12 at 14:19
1 Answers
0
From MySQL document:
SELECT * FROM tablename ORDER BY RAND() LIMIT 1
works for small tables, but once the tables grow larger than 300,000 records or so this will be very slow because MySQL will have to process ALL the entries from the table, order them randomly and then return the first row of the ordered result, and this sorting takes long time. Instead you can do it like this (atleast if you have an auto_increment PK):
SELECT MIN(id), MAX(id) FROM tablename;
Fetch the result into $a
//php code
$id=rand($a[0],$a[1]);
SELECT * FROM tablename WHERE id>='$id' LIMIT 1
Joel
- 111
- 7