title: SELECT DISTINCT Extremely slow
body: <p>I have a query that is taking 48 seconds to execute as follows:</p>
  
  <pre><code>SELECT count(DISTINCT tmd_logins.userID) as totalLoginsUniqueLast30Days 
  FROM tmd_logins
  join tmd_users on tmd_logins.userID = tmd_users.userID 
  where tmd_users.isPatient = 1 AND loggedIn &gt; '2011-03-25' 
  and tmd_logins.userID in 
      (SELECT userID as accounts30Days FROM tmd_users
      where isPatient = 1 AND created &gt; '2012-04-29' AND computerID is null)
  </code></pre>
  
  <p>When I remove the <code>DISTINCT</code> keyword it takes less than 1 second, so it seems the bottle neck lies within that.</p>
  
  <p>The database adds an entry to the <code>tmd_logins</code> table every time a user logs into the system. I am trying to get a total count of all users that are patients that have been created and logged in within a given period of time, such as in the last 30 days.</p>
  
  <p>I have tried removing the DISTINCT keyword and adding <code>group by tmd_logins.userID</code> to the statement but the performance issue remains. </p>
  
  <p>Table <code>tmd_logins</code> has about 300,000 records, <code>tmd_users</code> has about 40,000</p>
  
  <p>Is there a better way of doing this?</p>