On Jan 9, 2008, at 8:07 PM, Scott Marlowe wrote:
I could see a use for an approximate count(*) with where clause, just like I could see a use for the ability to retrieve random rows from a table without using order by random() on it. And those are both things that would require some form of hacking in the db that I'm certainly not capable of pulling off...
About returning random rows... I've successfully applied a scrolling cursor for that.
You need to scroll to the last row to find the size of the result set, but after that it's pretty easy to return random rows by scrolling to them (and marking them 'read' in some way to prevent accidentally returning the same row again).
It does require some specific application code though - doing it server side would mean to pass the query as a function argument (which still requires unnatural SQL statements in your application code) or write a function for each query (*cough*).
Performance was quite adequate (a few 100 ms) for a query returning random 5 rows from 3 joined tables or more, some of which had a few 100k rows. Calculating random() for each record in the result set (to sort on) was taking much longer. That was on a dual 64-bit opteron with 4GB RAM, iirc.
Of course a built-in statement would be preferable, I just felt like pointing out that order by random() isn't necessarily the best alternative ;)
Regards, Alban Hertroys. !DSPAM:737,478cb43e9496078213597! ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
