title: Optimise query to remove "Using where; Using temporary; Using filesort"
body: <p>I have a simple SQL query that i'm trying to optimise to remove "Using where; Using temporary; Using filesort". </p>
  
  <p>This is the table:</p>
  
  <pre><code>CREATE TABLE `special_offers` (
    `so_id` int(11) NOT NULL auto_increment,
    `so_lid` int(11) NOT NULL,
    `so_product_id` int(11) NOT NULL,
    `so_bonus_product` int(11) NOT NULL,
    `so_reverse_relate` tinyint(1) NOT NULL default '0',
    `so_discount_amount` varchar(6) NOT NULL,
    `so_start` date NOT NULL default '0000-00-00',
    `so_expiry` date NOT NULL default '0000-00-00',
    `so_active` tinyint(1) NOT NULL,
    `so_archived` tinyint(4) NOT NULL default '0',
    `so_added` datetime NOT NULL,
    PRIMARY KEY  (`so_id`),
    KEY `so_archived` (`so_archived`),
    KEY `so_active` (`so_active`),
    KEY `so_start` (`so_start`),
    KEY `so_expiry` (`so_expiry`),
    KEY `so_product_id` (`so_product_id`),
    KEY `so_bonus_product` (`so_bonus_product`),
    KEY `so_lid` (`so_lid`)
  ) ENGINE=MyISAM AUTO_INCREMENT=65610 DEFAULT CHARSET=latin1
  </code></pre>
  
  <p>This is the query:</p>
  
  <pre><code>SELECT `so_id` , `so_lid` , `so_bonus_product` , `so_product_id`
  FROM `special_offers`
  WHERE `so_archived` = '0'
  AND `so_active` = '1'
  AND (
  `so_start` &lt;= CURDATE( )
  OR `so_start` = '0000-00-00'
  )
  AND (
  `so_expiry` &gt;= CURDATE( )
  OR `so_expiry` = '0000-00-00'
  )
  GROUP BY `so_lid`
  </code></pre>
  
  <p>An EXPLAIN:</p>
  
  <pre><code>mysql&gt; EXPLAIN SELECT `so_id` , `so_lid` , `so_bonus_product` , `so_product_id` FROM `special_offers` WHERE `so_archived` = '0' AND `so_active` = '1' AND ( `so_start` &lt;= CURDATE( ) OR `so_start` = '0000-00-00' ) AND ( `so_expiry` &gt;= CURDATE( ) OR `so_expiry` = '0000-00-00' ) GROUP BY `so_lid`;
  +----+-------------+-------------------+------+------------------------------------------+-------------+---------+-------+------+----------------------------------------------+
  | id | select_type | table             | type | possible_keys                            | key         | key_len | ref   | rows | Extra                                        |
  +----+-------------+-------------------+------+------------------------------------------+-------------+---------+-------+------+----------------------------------------------+
  |  1 | SIMPLE      | special_offers    | ref  | so_archived,so_active,so_start,so_expiry | so_archived | 1       | const | 7684 | Using where; Using temporary; Using filesort |
  +----+-------------+-------------------+------+------------------------------------------+-------------+---------+-------+------+----------------------------------------------+
  </code></pre>