title: SQL: How to find similar Blogs by its followers
body: <p>For a microblogging site, where creating a blog is very easy, so I work with +100k Blogs in mind as a possible case scenario.</p>
  
  <p>I want to show on every Blog the related Blogs, my first idea is to use followers, because I think is the best data I have to do that. I’m open to any idea, but I will continue explaining what I’m trying to do.</p>
  
  <p>I plan to later process the selected data to make the relations, but to keep it simple I will focus on the first query:<br>
  <strong>Select which Blogs are also following, the followers of a Blog.</strong>  </p>
  
  <p>I want to make it efficient, it will have to run 100k times if there are 100k Blogs. It will only need to run occasionally, but it might "crash" if surpasses available resources.</p>
  
  <p>For the first part of the query I do a <code>LIMIT</code> and use random data:</p>
  
  <pre><code>SELECT user_id FROM follows WHERE blog_id=4 ORDER BY rand() LIMIT 2000
  </code></pre>
  
  <p>So if a Blog has 20k followers I don’t need to get them all to have enough data, and <code>rand()</code> provides that randomness, so I will not be just selecting old inactive or new users, but an average user.</p>
  
  <p><strong>The biggest concern is on the second part</strong>, where if those 2000 users follow as a whole an 80% of the Blogs, that would make a temporary table of 80k values, that have to be ordered.</p>
  
  <pre><code>SELECT blog_id, COUNT(*) FROM (SELECT user_id FROM follows WHERE blog_id=4 ORDER BY rand() LIMIT 2000)fw JOIN follows ON fw.user_id = follows.user_id GROUP BY blog_id
  </code></pre>
  
  <p>This query will start Counting all the followers (from the selected users) of each <code>blog_id</code> <code>ASC</code>. If I add a <code>LIMIT</code> it will not reach the newer Blogs, and if I apply <code>ORDER BY rand()</code> it will just count some Blogs, 100% of followers or none.</p>
  
  <p>How would you approach this? Do you see any improvement to my query/approach?</p>
  
  <pre><code>CREATE TABLE `follows` (
   `user_id` int(10) unsigned NOT NULL,
   `blog_id` int(10) unsigned NOT NULL,
   PRIMARY KEY (`user_id`,`blog_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
  </code></pre>
  
  <p>PD: Later I plan to check one list against the others, so a famous Blog does not become most related to most of the Blogs, there will need to be a "collision" of followers from both Blogs to rank in their related lists.</p>