title: Understanding how to design MySQL indexes for good performance
body: <p>Through trial and error, I've arrived at a good index for this query, but I'd really like to understand why this and only this index helps, and how to avoid having to repeat the t&amp;e next time. </p>
  
  <p>The InnoDB table structure for a log table is:
  <a href="https://i.stack.imgur.com/KFojj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KFojj.png" alt="Log table structure"></a></p>
  
  <p>This is my query—it's looking for all users who have one kind of action in the log, but <em>not</em> another kind of action. It's also restricting to certain values of <code>org</code> and a certain date range.</p>
  
  <pre><code>SELECT DISTINCT USER AS 'Dormant Users'
  FROM db.log
  WHERE `action` = @a1
    AND `org` = @orgid
    AND `logdate` &gt;= @startdate
    AND USER NOT IN (SELECT DISTINCT USER
               FROM db.log
              WHERE `action` = @a2
                AND `org` = @orgid
                AND `logdate` &gt;= @startdate)
  ;
  </code></pre>
  
  <p>With no indexes, this takes about 21 seconds, and <code>EXPLAIN</code> shows this:</p>
  
  <p><a href="https://i.stack.imgur.com/j2rPa.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/j2rPa.png" alt="EXPLAIN with no indexes"></a></p>
  
  <p>So, I thought having an index on <code>org</code>, <code>logdate</code>, and <code>action</code> might help. And it does—if I create an index on those columns in <em>that precise order</em>, the query time is reduced to about 0.3s, and the <code>EXPLAIN</code> output is now:</p>
  
  <p><a href="https://i.stack.imgur.com/cqswk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cqswk.png" alt="EXPLAIN with good index"></a></p>
  
  <p>But, if I change the order of the columns within the index, or even just add another, unrelated index (say on the <code>user</code> column), the query takes about 2 seconds.</p>
  
  <p>So, how can I understand and even design the index to perform well based on that query, and avoid the rather degenerate case of adding another index and harming performance? Or is it just a case of test and see what works?</p>