Fast random row in MySQL

August 3rd, 2007

Building upon the code provided in this article: http://www.greggdev.com/web/articles.php?id=6
I created the following MySQL query which will return a random row in a table you say.

SELECT *, COUNT(*) AS isvalid, (SELECT MAX(id) FROM mytable) AS nump, (SELECT MIN(id) FROM mytable) AS minp FROM mytable GROUP BY id HAVING id = FLOOR(minp + (RAND() * (nump - minp))) AND isvalid IS NOT NULL LIMIT 0,1;

Ofcourse, replace mytable with the name of your table and id with the primary key column of your table.

It’s a quick thing, but I’m sure it’s better than ORDER BY RAND(), as that can be slow (sloooooooooooooooooow).

Enjoy! And feel free to report any bugs.

EDIT: This will only work for a single row in most cases though, if you have any suggestions on how to make it work for a variable number of rows, I’m open for suggestions ;)

Welcome

July 20th, 2007

I didn’t think it’d ever come to this. Me setting up a blog.

But I guess I just need a place to rant.

I have recently posted the following on the Newgrounds forums, and I’m sure it deserves a place on my blog. Enjoy!

Read the rest of this entry »