php - Selecting items from MySQL for a status update feed efficiently while accounting for possible spam -

i creating "status update" feed users can post general messages can viewed other users (think status updates in facebook, twitter tweets, etc.). first of all, each post assigned rank score @ time post created , saved database. rank score combined time decay function used assign "true rank" each post @ time feed generated , presented user. true rank how items sorted.

a complexity arose when introducing strategy combat "spam" (we don't want users able post rapidly , hog top spot in feed). strategy track how many times user has posted in, say, last hour. every subsequent post added within last hour, assigned rank score penalized. more posts user within last hour, greater rank score penalty. idea here push potential spam further down feed, while still giving chance display.

following illustrates how feed sorted after 4 items posted same user if we're not accounting spam.

item 1: posted 1 min ago. true rank = 0.99. user_id = 666. item 2: posted 2 min ago. true rank = 0.98. user_id = 666. spam. item 3: posted 3 min ago. true rank = 0.97. user_id = 666. spam. item 4: posted 4 min ago. true rank = 0.96. user_id = 666. spam. item 5: posted 5 min ago. true rank = 0.95. user_id = 100. item 6: posted 6 min ago. true rank = 0.94. user_id = 100. 

following desired. notice posts user_id = 666 fall down feed after first post.

item 1: posted 1 min ago. true rank = 0.99. user_id = 666. item 5: posted 5 min ago. true rank = 0.95. user_id = 100. item 6: posted 6 min ago. true rank = 0.94. user_id = 100. item 2: posted 2 min ago. true rank = 0.88. user_id = 666. spam. item 3: posted 3 min ago. true rank = 0.77. user_id = 666. spam. item 4: posted 4 min ago. true rank = 0.66. user_id = 666. spam. 

problem

i have strategy doing this. mentioned above, i'm tracking number of posts per hour each user , deducting rank score if necessary. no problem.

the problem arises when thinking how efficiently select items database each time we're preparing feed user. performance. want select, say, 1000 feed items @ time (1000 initially, , 1000 each subsequent fetch if user requests more). if first 1000 items or spam? selecting items , preparing feed technically work, number of items in system potentially large.

i'm struggling create algorithm selects items feed without need pull out large amounts @ time, , yet gives every feed item chance display on feed, if rank extremely low.

artificial solutions letting user post n items per hour not option.

for it's worth, tools @ disposal mysql, memcached, , php.

thanks in advance help.

i think way of making reads efficient work out when exclude posts @ insert time (perhaps have separate notifications table , use rate limiting, e.g. leaky bucket algorithm, work out ones exclude).

to close want data have @ moment http://sqlfiddle.com/#!2/c20ac/8

it's saying "only allow 1 post show each user in every n posts". you'd have tune n value (5 in example) rate of data have works reasonably data.

feature-wise nicer version (if store timestamp of post) division based on time of post, difference between time , time of post. think work out slower though you'd grouping on derived column.