title: MySQL - Selecting related items in a table
body: <p>I'm tracking user visits to course pages on our website. I'm doing this so that for any given course (aka product) I can pull up a list of the top other course pages that users visited, who also visited the current page - just like Amazon's "Customers Who Viewed This Item Also Viewed" feature.</p>
  
  <p>What I have is working, but as the data collected continues to grow, the query times are getting considerably slower and slower.  I've now got approx 300k records and the queries are taking 2+ seconds each. We're expecting to start trimming the data when we reach about 2M records, but given the performance problems we're currently facing, I don't think this will be possible. I would like to know if there is a better approach to how I'm doing this.</p>
  
  <p>Here's the gory details...</p>
  
  <p>I've got a simple three column InnoDB table containing the user id, course number and a timestamp. The user id and course number fields are indexed, as is the user id/course number combined. Here's the table schema:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `coursetracker` (
    `user` varchar(38) NOT NULL COMMENT 'user guid',
    `course` char(8) NOT NULL COMMENT 'subject code and course number',
    `visited` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'last visited time',
    UNIQUE KEY `ndx_user_course` (`user`,`course`),
    KEY `ndx_user` (`user`),
    KEY `ndx_course` (`course`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='tracking user visits to courses';
  </code></pre>
  
  <p>Data in the table looks like this:</p>
  
  <pre><code>user                                   | course   | visited
  =======================================|==========|====================
  {00001A4C-1DE0-C4FB-0770-A758A167B97E} | OFFC2000 | 2013-01-19 23:18:03
  {00001FB0-179E-1E28-F499-65451E5C1465} | FSCT8481 | 2013-01-30 13:12:29
  {0000582C-5959-EF2B-0637-B5326A504F95} | COMP1409 | 2013-01-13 16:09:42
  {0000582C-5959-EF2B-0637-B5326A504F95} | COMP2051 | 2013-01-13 16:20:41
  {0000582C-5959-EF2B-0637-B5326A504F95} | COMP2870 | 2013-01-13 16:25:41
  {0000582C-5959-EF2B-0637-B5326A504F95} | COMP2920 | 2013-01-13 16:24:40
  {00012C64-2CA1-66DD-5DDC-B3714BFC91C3} | COMM0005 | 2013-02-18 21:32:36
  {00012C64-2CA1-66DD-5DDC-B3714BFC91C3} | COMM0029 | 2013-02-18 21:34:04
  {00012C64-2CA1-66DD-5DDC-B3714BFC91C3} | COMM0030 | 2013-02-18 21:34:50
  {00019F46-6664-28DD-BCCD-FA6810B4EBB8} | COMP1409 | 2013-01-16 15:48:49
  </code></pre>
  
  <p>A sample query that I'm using to get the related courses to any given course (COMP1409 in this example), looks like this:</p>
  
  <pre><code>SELECT `course`,
         count(`course`) c
  FROM `coursetracker`
  WHERE `user` IN
          (SELECT `user`
           FROM `coursetracker`
           WHERE `course` = 'COMP1409')
      AND `course` != 'COMP1409'
  GROUP BY `course`
  ORDER BY c DESC LIMIT 10
  </code></pre>
  
  <p>The results of this query look like this:</p>
  
  <pre><code>course   | c
  =========|====
  COMP1451 | 470
  COMP1002 | 367
  COMP2613 | 194
  COMP1850 | 158
  COMP1630 | 156
  COMP2617 | 126
  COMP2831 | 119
  COMP2614 | 95
  COMP1911 | 79
  COMP1288 | 76
  </code></pre>
  
  <p>So, everything above works exactly as I'd like, other than the performance. The table is so simple that there's nothing left to index. The SQL query results in the data that I'm looking for. I'm out of ideas on how to do this faster. I'd appreciate any feedback on the approach.</p>