title: MySQL Query taking too long when using orderby on large dataset
body: <p>I am trying to improve the query time on a database that has around 20 million rows in the table:</p>
  
  <p>The following is the query I am running</p>
  
  <pre><code>`select DATE(timestamp) as timestamp, count(id) as authentications, count(distinct userid) as unique_users from `logs` where `org_id` = '54' and `timestamp` between '2016-09-14 09:00:00' and '2017-08-21 09:37:59' group by DATE(timestamp) order by `timestamp` asc`
  </code></pre>
  
  <p>The query takes about 20-30 seconds to complete (that particular <code>org id</code> has about 6-7million records its going through)</p>
  
  <p>According to the profiler its spending 95% of the time on sorting the result.</p>
  
  <p>I have an index that is being used as well (please see explain below):   </p>
  
  <p>1   SIMPLE  logs    </p>
  
  <pre><code>ref by_org_id_and_timestamp,org_id  by_org_id_and_timestamp 4   const   6231240 Using index condition; Using where; Using filesort  
  </code></pre>
  
  <p>Table structure is:</p>
  
  <pre><code>id  int(10) unsigned    NO  PRI     auto_increment  
  org_id  int(11) NO  MUL         
  profile varchar(256)    NO              
  linehash    varchar(50) NO  UNI         
  timestamp   datetime    NO              
  userid  varchar(256)    NO              
  server_id   varchar(1024)   NO              
  access_id   int(11) NO              
  </code></pre>
  
  <p>Any ideas as to why its taking so long or is 20-30 seconds the best I am going to get?</p>
  
  <p>Thanks for looking (and hopefully your help!)</p>
  
  <p>------ SHOW CREATE TABLE logs ------</p>
  
  <p><code>id int(10) unsigned NOT NULL AUTO_INCREMENT,
    org_id int(11) NOT NULL,
    profile varchar(256) COLLATE utf8_unicode_ci NOT NULL,
    linehash varchar(50) COLLATE utf8_unicode_ci NOT NULL,
    timestamp datetime NOT NULL,
    userid varchar(256) COLLATE utf8_unicode_ci NOT NULL,
    server_id varchar(1024) COLLATE utf8_unicode_ci NOT NULL,
    access_id int(11) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY logs_linehash_unique (linehash),
    KEY by_org_id_and_timestamp (org_id,timestamp),
    KEY org_id (org_id),
    KEY by_org_id_and_timestamp_userid (org_id,timestamp,userid(255))</code></p>