[oclug]MySQL question

[Ian Wormsbecker]

On January 19, 2003 09:41 pm, Joe Burpee wrote:
> On Sun, Jan 19, 2003 at 05:29:30PM -0500, B McKee wrote:
> > Thanks guys for the good info - When I hit a head scratcher like that I
> > start questioning everything.  If it's generating a new number for each
> > each row, then I'm not likely to get the results I want.  Sorting and
> > then picking one sounds like a lot of processing for what should be a
> > simple task.  Since the query is to be made by a perl script (that's the
> > next step) I think I'll have perl generate the random number before I
> > pass the query to the database.   It will take a second query to
> > determine the number of rows first, but come to think of it, I don't
> > think I could have done that with a single query anyway.
>
> I don't think that's true.  For example, if you want one random row you
> could use just the single query
>
> SELECT * FROM sponsor ORDER BY rand() LIMIT 1;
>

How does this ORDER BY rand() work? What does the ORDER BY do with the rand() 
arg do? I looked at the mysql docs and the only part I could find talking 
about ORDER BY was here:

http://www.mysql.com/doc/en/SELECT.html

I noticed that you can use an unsigned_integer after ORDER BY. What 
significance is this unsigned integer? I have only ever used column names 
after an ORDER BY clause.

Looking at the postgresql docs ( I typically use postgresql so...) here:

http://www.postgresql.org/idocs/index.php?queries-order.html

it appears that putting a number after the ORDER BY signifies the column to 
use. So if you use 1, it will order based on the first column.

Is this what the original OP wanted, to pick based on a random column? I would 
think this would result in a similar few tuples being selected each time 
given a slowly changing dataset.


Unfortunately I am at a loss as to an efficient way to select a single tuple 
at the moment. Maybe by morning :)


> If you look at the MySQL optimizations at
> http://www.mysql.com/doc/en/LIMIT_optimisation.html
> you'll notice that
>   "If you use LIMIT # with ORDER BY, MySQL will end the sorting as
>   soon as it has found the first # lines instead of sorting the
>   whole table."

I should hope it does :)

Ian