Reset column autoindex?

[iceflyin]

I have a problem... I'm trying to get random rows from a table, however data keeps being deleted and added, and I need to reset the autoindex index column.

Heres the code I want to use:
http://akinas.com/pages/en/blog/mysql_random_row/
SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 1;


However, this causes problems of "not being random" when there is large areas of deleted rows.

Can I simply delete the primary key, and re-create the autoindex every little while?

[CFMaBiSmAd]

Don't bother resetting an auto-increment index (your code should not care if there are gaps and neither should you.)

Do what the mysql manual recommends -
However, you can retrieve rows in random order like this:

mysql> SELECT * FROM tbl_name ORDER BY RAND();

ORDER BY RAND() combined with LIMIT is useful for selecting a random sample from a set of rows:

mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d -> ORDER BY RAND() LIMIT 1;