[thelist] MySQL queries and AUTO_INCREMENT

[Mattias Thorslund]

Max Schwanekamp wrote:
> 1. Get the lowest and highest id value from mytable:
>     SELECT min(id) AS firstrow, max(id) AS lastrow FROM mytable
> 2. Generate a random integer between $firstrow and $lastrow:
>     $x = mt_rand($firstrow,$lastrow);
> 2. SELECT [fields] FROM mytable WHERE id >= $x LIMIT 1
> 3. If no data returned, query once more:
>     SELECT [fields] FROM mytable WHERE id < $x LIMIT 1
> 4. If still no data, your table is empty; raise an error.
>
> With this method there is no coupling of the random number routine in 
> PHP and the PK column 'id' in your table, aside from the requirement of 
> it being an integer, but the original post was about AUTO INCREMENT 
> which only works on integer columns anyway.
>   

Interesting technique.

But it looks like some rows would be more likely to be chosen than
others. Consider if the IDs are [1, 2, 3, 100]. Then, picking a random
number between 1 and 100 will result displaying the row with ID 100 for
97% of the time. Of course that's an extreme example but for each
missing number, the likely selection of the following record is much
larger than the others (one missing number doubles the likelihood of
selecting the next record, two missing numbers will triple it)...

What's the use of point 3? The (second) point 2 should return a row if
there are rows in the table.
If there are no rows in the table, point 1 will return NULL in both
firstrow and lastrow, which is where you should raise the error.


I agree with John Brooking: Use a separate sequence field, and update
it. Or, if the number of records will be not too large, use ORDER BY
RAND() LIMIT 1.

Mattias