|
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.
|