From: Jan Steinman
Date: October 31 2011 2:03am
Subject: Re: large temp files created by mysql
List-Archive: http://lists.mysql.com/mysql/226201
Message-Id: <191D14E6-E244-4B94-B39E-B8FD277C5830@Bytesmiths.com>
MIME-Version: 1.0 (Apple Message framework v1084)
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable

Actually, having tried that, you still need the ORDER BY RAND() in =
there. Otherwise, I keep getting the same record over and over. But it =
surely cuts way down on the number of rows that need to be sorted.

So if your table size is fairly stable, and you pick a good number for =
the WHERE constant, you can make this quite speedy.

Still, it seems there should be a better way...

On 30 Oct 11, at 18:51, Jan Steinman wrote:

>> From: mos <mos99@stripped>
>>=20
>>=20
>> At 10:34 AM 10/24/2011, you wrote:
>>> select id from table order by rand() limit 1;
>>> is doing as example a dumb temporary table with the full size
>>=20
>> Because it has to sort the entire table, then it returns the one row. =
This of course is extremely inefficient. :)
>=20
> That is absolutely incredible and counter-intuitive, and (as you say) =
extremely inefficient!
>=20
> This is used everywhere. Perhaps it is one of the biggest =
"anti-patterns" in SQL. I just checked two different SQL "cookbook" =
sites, and they both recommend ORDER BY RAND().
>=20
> I just googled around a bit, and found that putting RAND() in the =
WHERE clause is very efficient:
>=20
> SELECT id FROM table WHERE RAND() < 0.01 LIMIT 1
>=20
> The comparison constant can be optimized for the number of rows you =
have. The above returns the first record of 1% of the table. If you have =
a million rows, you might want to bump that to something like 100 parts =
per million or so.
>=20
> But really, folks, this is something so ubiquitous and so recommended, =
why can't the query optimizer look out for ORDER BY RAND() and simply =
skip the table sort and just grab some record? (Hopefully using =
something better than Knuth's LCRNG...)
>=20
> ----------------
> Learning to think wholistically requires an overriding, or reversal, =
of much of the cultural heritage of the last few hundred years. -- David =
Holmgren
> :::: Jan Steinman, EcoReality Co-op ::::
>=20

----------------
Within a few human generations, the low-energy patterns observable in =
natural landscapes will again form the basis of human system design =
after the richest deposits of fossil fuels and minerals are exhausted. =
-- David Holmgren
:::: Jan Steinman, EcoReality Co-op ::::