MySQL - creating an SQL Algorithm to determine random 'popular' content

I'm looking to create an SQL query (in MySQL) that will display 6 random, yet popular entries in my web application.

My database has the followi开发者_如何学Cng tables:

• favorites
• submissions
• submissions_tags
• tags

• users

  1. submissions_tags and tags are cross-referencing tables that give each submission a certain number of tags.
  2. submissions contains boolean featured, int downloads, and int views, all three of which I'd like to use to weight this query with.
  3. The favorites table is again a cross-reference table with the fields submission_id and user_id. Counting the number of times each submission has been favorited would be good to weigh the results with.

So basically I want to select 6 random rows weighted with these four variables - featured, downloads, views, and favorite count. Each time the user refreshes the page, I want a new random 6 to be selected. So maybe the query could limit it to 12 most-recent but only pluck 6 random results out to show. Is that a sensible idea in terms of processing etc.?

So my question is, how can I go about writing this query? Where should I begin? I am using PHP/CodeIgniter to drive this site with. Is it possible to get the entire lot in one query, or will I have to use multiple queries to do this? Or, do I need to simplify my ideas?

Thanks,

Jack

I've implemented something similar to this before. The route I took was to have a script run on the server every XX minutes to fill a table with a pool of items (say 20-30 items). Then the query to use in your application would be randomly pick 5 or so from that table.

Just need to setup an algorithm to select those 20-30 items. @Emmerman's is similar to what I used before to calculate a popularity_number where I took weights of multiple associations to the item (views, downloads, etc) to get an overall number. We also used an age to make sure the pool of items stayed up-to-date. You'll have to tinker with the algorithm over time to make sure the relevant items are being populated.

The idea is to calc some popularity which can be for e.g.

popularity = featured*W1 + downloads*W2 + views*W3 + fcount*W4

Where W1-W4 are constant weights.

Then add some random number to popularity and sort for it.