[thelist] MySQL queries and AUTO_INCREMENT

[Matt Warden]

On 4/7/06, Mattias Thorslund <mattias at thorslund.us> wrote:
> 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)...

His disclaimer was for > 10,000 rows, and it doesn't sound like this
is an application where uniformity is a huge issue.

> What's the use of point 3? The (second) point 2 should return a row if
> there are rows in the table.

Only if you lock tables for writing in between these queries.

> 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.

I would suggest this either way. I'm not sure issuing 3 queries over
the network is more efficient than using this method (although, I
haven't done any testing).

--
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.