title: How to optimize MySQL query for a large database
body: <p>I've noticed a serious problem recently, when my database increased to over 620000 records. Following query: </p>
  
  <pre><code>SELECT *,UNIX_TIMESTAMP(`time`) AS `time` FROM `log` WHERE (`projectname`="test" OR `projectname` IS NULL)  ORDER BY `time` DESC LIMIT 0, 20
  </code></pre>
  
  <p>has an execution time about 2,5s on a local database. I was wondering how can I speed it up?</p>
  
  <p>The EXPLAIN commands produces following output:</p>
  
  <pre><code>ID: 1
  select type: SIMPLE
  TABLE: log
  type: ref_or_null
  possible_keys: projectname
  key: projectname
  key_len: 387
  ref: const
  rows: 310661
  Extra: Using where; using filesort
  </code></pre>
  
  <p>I've got indexes set on projectname, time columns.</p>
  
  <p>Any help?</p>
  
  <p>EDIT: Thanks to ypercube response, I was able to decrease query execution time. But when I only add another condition to WHERE clause (AND severity="changes") it lasts 2s again. Is it a good solution to include all of the possible "WHERE" columns to my merged-index? </p>
  
  <pre><code>ID: 1 
  select type: SIMPLE 
  TABLE: log 
  type: ref_or_null 
  possible_keys: projectname 
  key: projectname 
  key_len: 419 
  ref: const, const 
  rows: 315554 
  Extra: Using where; using filesort
  </code></pre>
  
  <p>Table structure:</p>
  
  <pre><code>   CREATE TABLE `log` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `projectname` VARCHAR(128) DEFAULT NULL,
    `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `master` VARCHAR(128) NOT NULL,
    `itemName` VARCHAR(128) NOT NULL,
    `severity` VARCHAR(10) NOT NULL DEFAULT 'info',
    `message` VARCHAR(255) NOT NULL,
    `more` TEXT NOT NULL,
    PRIMARY KEY (`id`),
    KEY `projectname` (`severity`,`projectname`,`time`)
  ) ENGINE=INNODB AUTO_INCREMENT=621691 DEFAULT CHARSET=utf8
  </code></pre>