[thelist] MySQL queries and AUTO_INCREMENT
Max Schwanekamp
lists at neptunewebworks.com
Fri Apr 7 11:38:25 CDT 2006
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/
More information about the thelist
mailing list