Re: count(*) and bad design was: Experiences with extensibility

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!