Speed up my mysql query please - order by rand()

[escoman]

SELECT *
FROM mytable
WHERE vonline = 'Y'
ORDER BY RAND( ) DESC
LIMIT 44

I need a good alternative to rand() that is fast. 0.01> or so.

[fatfoo]

You ever heard the phrase "emp id is null"? Good luck.

[amphibient]

there are a few things you can do. If the table is small - i see you only want 44 records - if it's no larger than 100 or so records (that's an arbitrary number) you could just pull all of the records and pick them randomly using your programming language of choice (PHP?).

if the table is huge, you could do something like

SELECT id, col1, col2, ... , colN FROM tab WHERE id IN (SELECT id FROM tab WHERE conditions ORDER BY RAND() LIMIT m)

(taken from the dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html comments)

How random do you want it to be?

[amphibient]

Also... if you have a bit more infrastructure available - specifically meaning you can run memcached on a server or two (or many) create a cache entry for the stuff you're interested in retrieving randomly, and use your programming language to pick a series of "random" numbers between your minimum and maximum values (assign each record in your memcached stuff an integer number key) and pull them this way...

[amphibient]

Also (again) do you have any indexes on your table?

[escoman]

Originally Posted by amphibient

Also (again) do you have any indexes on your table?

Yes i have indexes. About 15 000 rows.

SELECT id, col1, col2, ... , colN FROM tab WHERE id IN (SELECT id FROM tab WHERE conditions ORDER BY RAND() LIMIT m)

doesn't work in my version of mysql 4.1.22.

I want it to be as random as possible. Pulling them like 1 2 3 or 21 22 23 is out of question.

[amphibient]

unfortunately, it seems a lot of the goodies that would make this easy are missing in mysql 4 (views, sub-selects like the one I had mentioned, etc.) There could, however, be an advantage in selecting unique IDs (something indexed, preferably a primary key) using your WHERE clause:

SELECT id FROM tablename WHERE vonline = 'Y'

and then randomly picking unique IDs with your programming language - and then pulling those records specifically.

so in pseudo-code:

IDs = "SELECT id FROM tablename WHERE vonline = 'Y'"

NUMBER_OF_IDs = count(IDs) // count() provides the number of records returned

// pull 44 (in this case) IDs using PHP's rand() or mt_rand()

// select those 44 records from the database.

/end pseudo-code...

The issue with this is 2 database queries. The benefit of this is that you're not asking the database to do anything complex.

If you're using PHP, I'd recommend using mt_rand() instead of rand() because in some testing i did, it was noticeably faster.

I definitely recommend testing something like this before jumping in whole hog too.. but if you'd like some off-the-board help, shoot me an email at contact at abstractinitiative dot com.

The things that are going to drive/affect the speed of something like this are:

1) the speed and CPU availability of your web and db server(s).
2) the connection between your web and database server(s).


Another item to consider is that mysql joins are usually pretty efficient too, consider testing out a table that contains a column that has 1 record, 'Y' - this could be a hack at a 'view' of only looking at the records in 'tabname' that have the column 'vonline' set to 'Y' - and then randomly picking values from there.. The join *may or should* be a bit faster than the plain WHERE clause.