How to retrieve a row, biased by populatity?

[Fahad Khan]

On Wed, Aug 22, 2012 at 10:15 AM, David Hodgkinson <davehodg at gmail.com>wrote:

>
> On 21 Aug 2012, at 21:54, Fahad Khan <fahad.aj.khan at gmail.com> wrote:
>
> > SELECT * FROM band ORDER BY ranking - RAND() * $BIAS DESC LIMIT 1;
> >
> > Fahad.
>
>
> I like this one best. I know I have 850k rows, just pick the n'th on
> some form of log curve.
>

Ah the devil's in the detail. I'm not sure exactly what you mean. But...

1. You know you have 850k rows. We didn't.
2. Its not any form of a log curve.
3. Read what follows. I'm sure there's more detail. Bare in mind this
Debian Lenny stock install on a pretty humble desktop.

mysql> show create table bands;
| bands | CREATE TABLE `bands` (
  `name` text,
  `ranking` int(11) DEFAULT NULL,
  KEY `ranking_idx` (`ranking`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

mysql> SELECT COUNT(*) FROM bands;
+----------+
| COUNT(*) |
+----------+
|   850000 |
+----------+
1 row in set (0.01 sec)

mysql> SELECT * FROM bands ORDER BY ranking - RAND() * 5000 DESC LIMIT 1;
+---------------------------------+---------+
| name                            | ranking |
+---------------------------------+---------+
| prompt socializes callus period |  849973 |
+---------------------------------+---------+
1 row in set (1.26 sec)



mysql> show create table band;
+-------+-------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create
Table
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------+
| band  | CREATE TABLE `band` (
  `name` text,
  `ranking` int(11) NOT NULL,
  PRIMARY KEY (`ranking`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM band;
+----------+
| COUNT(*) |
+----------+
|   850000 |
+----------+
1 row in set (0.35 sec)

mysql> SELECT * FROM band ORDER BY ranking - RAND() * 5000 DESC LIMIT 1;
+---------------------------------------------+---------+
| name                                        | ranking |
+---------------------------------------------+---------+
| biscuit Egyptianizations Jamaica consequent |  849979 |
+---------------------------------------------+---------+
1 row in set (1.76 sec)


$ pg_dump dave_test --schema-only -t band
...
CREATE TABLE band (
    name text,
    ranking integer NOT NULL
);
...
ALTER TABLE ONLY band
    ADD CONSTRAINT band_pkey PRIMARY KEY (ranking);
...

dave_test=> select count(*) from band;
 count
--------
 850000
(1 row)

dave_test=> SELECT * FROM band ORDER BY ranking - RANDOM() * 5000 DESC
LIMIT 1;
     name     | ranking
--------------+---------
 sank fission |  849994
(1 row)

Time: 372.286 ms