Sumeet wrote:
Thanks Buddy, really appreciate ur help on this
....problem solved...
Is there any way this query can be optimized...i'm running it on a
huge table with joins
ORDER BY rand() is rather slow on large datasets, since the db has to
actually generate a random value for each row in the table, before being
able use it to sort by. Preferable ways to do this include f.ex:
SELECT max(id) FROM table;
SELECT * FROM table WHERE id > [rand value between 0 and max(id)] ORDER
BY id LIMIT 1;
This means you need to execute 2 queries, and it can also be a good idea
to somehow cache the number of rows/largest ID of the table, for quicker
performence.
You can find an interesting discussion on this topic at
http://thedailywtf.com/Comments/Finding_Random_Rows.aspx (yeah, I know.
thedayilywtf.com isn't normally what I'd use as a reference for anything
:-) )
--
Tommy
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
