How do I display random blog posts, favoring posts with high ratings?

Let's say I have a Blog posts table that has a rating field which indicates the quality of the post. What is the most efficient way to randomly find a post, with a higher chanc开发者_如何学运维e of returning a highly ranked post?

I will be implementing this in PHP, MySQL, and possibly Lucene.

An easy solution would be to include calls to RAND() and to the rating column and multiplying them together:

SELECT title, content FROM blog_posts ORDER BY (rating + 1) * RAND() DESC LIMIT 1;

If you found this gave too much precedence to items with a high rating, you could use SQRT:

SELECT title, content FROM blog_posts ORDER BY SQRT(rating + 1) * RAND() DESC LIMIT 1;

You could use a "weighted random" ordering, such as:

SELECT title, body FROM posts ORDER BY (score+1) * RAND() DESC LIMIT 5

The +1 is there to allow posts with 0 score to be selected. Depending on the average score of your post you may have to multiply the score by another constant factor (e.g. 0.5*score+1).

Depending on the distribution of your scores you may want to transform your score, for instance with LOG(score) or SQRT(score).

SQL COMMAND :

SELECT * FROM posts WHERE id = '$randomly_generated_ids' ORDER BY ratings ASC

for example $randomly_generated_ids can be "13,10,5,20"

Details for : ORDER BY