title: MySQL efficient select / order on a range query with multiple / single column indices
body: <p>I'm trying to do the most efficient select on a table with 3 million records.</p>
  
  <p><strong>First some detailed info</strong></p>
  
  <p><strong>Table:</strong></p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `activities_index` (
    `id` int(9) NOT NULL auto_increment,
    `activity_id` int(6) NOT NULL,
    `activity_status_id` int(2) NOT NULL,
    `activity_source_id` int(6) default NULL,
    `account_id` int(6) default NULL,
    `owner_account_id` int(4) default NULL,
    `date` date NOT NULL,
    `is_event` int(1) NOT NULL,
    `name` varchar(255) collate utf8_unicode_ci NOT NULL,
    `content` longtext collate utf8_unicode_ci,
    `location_name` varchar(255) collate utf8_unicode_ci default NULL,
    `location_content` longtext collate utf8_unicode_ci,
    `meta_keywords` varchar(255) collate utf8_unicode_ci default NULL,
    `thumb_filename` varchar(255) collate utf8_unicode_ci default NULL,
    `popular` int(1) NOT NULL default '0',
    `price` float default NULL,
    `city_id` int(9) default NULL,
    `province_id` int(4) default NULL,
    `country_id` int(4) default NULL,
    `activity_location_id` int(6) NOT NULL,
    `lat` decimal(10,6) default NULL,
    `lng` decimal(10,6) default NULL,
    `activity_modified` datetime default NULL,
    `activity_created` datetime NOT NULL,
    `activity_location_modified` datetime default NULL,
    `activity_location_created` datetime NOT NULL,
    `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    PRIMARY KEY  (`id`),
    KEY `is_event_idx` (`is_event`),
    KEY `activity_id_idx` (`activity_id`),
    KEY `status_city_idx` (`activity_status_id`, `city_id`),
    KEY `date_idx` (`date`),
    FULLTEXT KEY `txt_fields_idx` (`name`,`location_name`,`meta_keywords`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=14865 ;
  </code></pre>
  
  <p><strong>Query:</strong></p>
  
  <pre><code>SELECT SQL_NO_CACHE * FROM `activities_index` WHERE 
  date BETWEEN '2009-12-23' AND '2010-1-23' AND
  activity_status_id = 1 AND
  city_id IN ('86', '84', '87', '2381', '453', '137', '1561', '1116', '1614', '2456', '512', '305', '443', '1182', '2229')
  ORDER BY date
  LIMIT 25
  </code></pre>
  
  <p><strong>About my index choice:</strong><br />
  The main problem is the range select on DATE. Why I'm not using an multiple column index based on what I believe is true, please correct me if I'm wrong:
  <a href="http://en.wikipedia.org/wiki/MySQL" rel="nofollow noreferrer">MySQL</a> doesn't use any indices after a range. So the an index (DATE, ACTIVITY_STATUS_ID, CITY_ID) would be useless.
  An order by on the index table is only correct when using the right prefix. So a mutiple column index(CITY_ID, ACTIVITY_STATUS_ID, DATE) would not give the correct ordered result since we want to order the data on the column DATE.</p>
  
  <p><strong>Explain:</strong><br />
  When doing a EXPLAIN on the query the possible_keys order is CITY_IDX, DATE_STATUS_IDX instead I think flipping that order to DATE_IDX, CITY_IDX would be more efficient when ordering by DATE.</p>
  
  <pre><code>id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra&lt;br /&gt;
  1  SIMPLE  activities_index  range  city_idx,date_idx  city_idx  5  NULL  1363  Using where; Using filesort
  </code></pre>
  
  <p><strong>My questions:</strong><br />
  How can I flip the order of the possible_keys?<br />
  Is there a better way of fixing the issue: an efficient select on a table with 3 million records?<br />
  Is my way of thinking the correct one?<br /><br /></p>