April 9, 2013 Hatem Ben Yacoub

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 !

Tagged: , ,

About the Author

Hatem Ben Yacoub Energy Engineer, Entrepreneur, ICT & eGov Consultant with over 15 years experience. Independent Open Data expert.

(HBY) Consultancy