MySQL RAND for social media

I don’t usually recommend auto-posting in social media however some applications require this behavior, and there should be an ethical way of doing this. I was experimenting a security project and wanted to share a tips for mysql usage with auto-tweeting.

The idea is to have twitter account which is connected to a database of pre-configured tweets, and tweeting randomly every hour or so. A pretty simple table of tweets :

--
-- Table structure for table `tweets`
--
CREATE TABLE IF NOT EXISTS `tweets` (
`id` bigint(21) NOT NULL AUTO_INCREMENT,
`tweet` varchar(150) NOT NULL,
`status` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=MyISAM;

A Cron job that select random tweet and share it to the world :

Select tweet from tweets order by RAND() Limit 0,1;

Now in addition to the “id, tweet”, you noticed that I have added a status field which will be incremented every time the message is tweeted. And to make the behavior not annoying to followers, I had to tweak this to keep tweets during the whole day, but in the same time not repeating myself !

So a quick check in the database I noticed these status values : 0,2,4,2,2,0,0,1,0,1…

Which means that some tweets have been shared 4 times and more, while others have not been tweeted at all. Let’s check this on database :

SELECT STATUS FROM `tweets` ORDER BY RAND( )

Refresh the results few times :


2,3,0,0,1,0...
2,0,0,0,0,0...
0,0,1,3,0,2...
1,1,0,0,1,1...


So you can notice that selecting randomly a tweet that have not being broadcasted before is not very common in this quick test. To tweak this you should order by status equal zero first, then randomize. Which will give something like :

SELECT id,STATUS FROM `tweets` ORDER BY status ASC,RAND()

Refresh the results few times again :


(23,0),(57,0),(40,0),(31,0)...
(28,0),(56,0),(22,0),(44,0),...
(100,0),(2,0),(33,0),(43,0),...


Now you notice that all status values are zeros, the non zeros values will be in the end, so in case I will add a limit the select will always give me a status zero !

Hope this help, enjoy !