Displaying a random "database row" daily. i.e Word of the Day

[eluser]TheFuzzy0ne[/eluser]
I wonder if it's any less intensive to do a COUNT(), and number the existing rows sequentially, and then pick a random row using a random number generated by PHP. Although every row is scanned, it doesn't have to be sorted, so might be a bit quicker. Another method might be to grab multiple rows:

Code:

$rand = rand(1, $total_db_rows);
$this->db->where('id >=', $rand);
$this->db->where('id <=', $rand+5);
$res = $this->db->get('words');

if ($res->num_rows())
{
    $row = $res->row_array(); # Grab the first row.
    return $row['word'];
}

I'll have to test this later. I used to have a database with 1,000,000 rows in it, and I need to recreate it again.

[eluser]Dam1an[/eluser]
I jsut realised, as interesting as this performance issue is... its totally irrelvant in this case
If its being run off a cron job, it doesn't matter if it takes 1 second or 1 minute to get a random row
I'm sure it doesn't matter if the word of the day changes at midnight, or a minute past

Now back to the performance aspect
Isn;t count on MySQL (the default engine) very fast, as it stored counts in a seperate table, so its more of a lookup then a real count

[eluser]TheFuzzy0ne[/eluser]
[quote author="Dam1an" date="1242834320"]Isn;t count on MySQL (the default engine) very fast, as it stored counts in a seperate table, so its more of a lookup then a real count[/quote]

I believe so, but I thought it only stored the number of rows in total, so if you're adding a WHERE clause, or dynamically assigning an extra field, I think it would still have the potential to be quite slow.

[eluser]slowgary[/eluser]
I read somewhere about a rand() query on a table with 2 million rows that took 12 minutes to execute. rand() does need to sandomly sort the entire table, so I don't think it's the way to go. It seems like this would be easy as long as you don't plan to delete any rows from the table - ever.

Wouldn't it just be 2 queries?

Code:

$count = $this->db->query("SELECT COUNT(*) AS count FROM words")->row_array()->count;
$count = mt_rand(0, $count);
$word = $this->db->query("SELECT word FROM words WHERE word_id = '$count'")->row_array()->word;

Caching it for 24 hours sounds like a good idea at first, but I don't think it would work well in practice. Correct me if I'm wrong, but it relies on a user visit to invalidate the cache, which means updating the word of the day won't necessarily happen every 24 hours. If traffic is slow you could even skip days. This means you'd never be able to archive or show the user yesterday's wotd reliably. You would also end up with confused users when the word is updated at seamingly random times.

[eluser]Dam1an[/eluser]
:o 12 minutes to sort 2 million rows?!?!?!
Does it create a temporary table, and then call rand 2 million times shrinking the size by one each time and copying the row to the temp table?

[eluser]slowgary[/eluser]
Actually, it turns out MySQL pauses in between sorts to take a coffee break.

Don't take my word for it, check it out -> http://www.greggdev.com/web/articles.php?id=6

Can you really complain about the 12 minutes? MySQL is a busy guy. If he were your coworker you'd be out of a job ;-P

[eluser]Evil Wizard[/eluser]
[quote author="slowgary" date="1242849086"]Can you really complain about the 12 minutes? MySQL is a busy guy. If he were your coworker you'd be out of a job ;-P[/quote]

MySQL is only one language ;o) lol, besides, after about 8 minutes I would have considered these options as plausible return values for mysql guy

1) Lots
2) Enough
3) Fowzands (Thousands)
4) Did you want coffee too?


Seriously though

Code:

CREATE TEMPORARY TABLE
    `database`.`temporary_table_name`
(
    `field1` int(11) unsigned NOT NULL default '' comment='my field comment',
    `field2` char(32) NOT NULL default '' comment='my field comment'
) ENGINE = MEMORY;

If you create a temporary table to hold data make sure you set the "ENGINE" to "MEMORY", this stops the temporary table from being written to disk and reduces access time because there is no need to read/write/access the disk to access the table data

[eluser]slowgary[/eluser]
But using rand() still requires MySQL to scan and sort the whole table. Getting a count() and creating a random number in PHP, then querying seems like a better approach.

I'm still a noob so what do I know.

[eluser]TheFuzzy0ne[/eluser]
I'm not sure whether this is a static list of words, or if new words are going to be added constantly, but is there any reason why you can't load the words in sequence? You could cache the ID and the word in a file, and then when the next days comes, you can grab the next entry from the database, and repeat the loop you reach the end.

[eluser]slowgary[/eluser]
But that won't make for a very interesting word of the day. It'll take years to go through each letter of the alphabet.