title: Best options to improve very slow 'related items' query
body: <p>I have two tables, one of images, and one that describes which images have been liked by which users. This second table just has a row per image/user combination. I want to write a query that will return which other images in the database has as many 'likers' in common as possible. So, the tables look like:</p>
  
  <pre><code>images
  +----+----------+
  | id | filename |
  +----+----------+
  
  images_users
  +----------+---------+
  | image_id | user_id |
  +----------+---------+
  </code></pre>
  
  <p>My query is:</p>
  
  <pre><code>SELECT images.filename FROM 
    images_users INNER JOIN images ON images.id = images_users.image_id 
     WHERE images_users.user_id IN ( .... list of user IDs ....) 
     AND NOT images.id = &lt;current image id&gt;
    GROUP BY images.id ORDER BY COUNT(images.id) DESC LIMIT 10;
  </code></pre>
  
  <p>Which is to say, find all the rows with matching 'likers', group by the image id, order by the count of that, and return the top 10. I also exclude the current image id, since of course that is otherwise always the top hit.</p>
  
  <p>When the list of user IDs I want to match with grows, this query can take minutes to run - although I am only running it on a home PC, not a big server.</p>
  
  <p>I kind of get the feeling that I'm stuck here, in the sense that my data is normalised according to the rules, but it's just a tough job for the server. I have indexes on all the columns in the join, and the query. </p>
  
  <p>Can I represent this information in another way, that might improve the query time?</p>
  
  <p>----- Edited for questions below -----</p>
  
  <p>The explain output for the query is below:</p>
  
  <pre><code>explain SELECT filename  from images_users inner join images on id = image_id 
  where image_users.user_id in
  (162,591,596,603,856,1654,1967,2219,3640,3847,3956,9201,10601) 
  and not images.image_id = 41578 group by images.id order by count(images.id) desc limit 10;
  
  "1" "SIMPLE"    "images_users"  "range" "u_id_2,u_id,i_id"  "i_id"  "4" \N  "9086346"   "Using where; Using temporary; Using filesort"
  "1" "SIMPLE"    "images"    "eq_ref"    "PRIMARY"   "PRIMARY"   "4" "images_users.i_id" "1" ""
  </code></pre>
  
  <p>There are about 10,000 users, about 1.5M images, and about 6M rows in the images_users table. </p>