[thelist] MySQL queries and AUTO_INCREMENT

[Max Schwanekamp]

Ken Schaefer wrote:
> Then you are completely mis-using the auto-increment as your primary key. PKs
> are there to identify the row uniquely. They are not there to fall within a
> range in some random number generator.

Ken is absolutely correct, you shouldn't be designing your database to 
fit in with your particular scripting technique.  Still, it's not so 
hard to let MySQL select a random row using a PK sequence that may have 
gaps.  If the table is small (a few thousand rows or less), you can just do:
SELECT [fields] FROM mytable ORDER BY RAND() LIMIT 1

The only problem with this is that when the table gets any heft to it 
(e.g. 10,000+ rows) the query gets slow.  So, assuming your PK is an 
integer type named 'id', the idea then is:
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.
-- 
Max Schwanekamp
http://www.neptunewebworks.com/