title: Query not using indexes in MySQL
body: <p>I am running a query in MySQL but it is taking lot of thime. When I do an explain on this I can sse it is not using index.</p>
  
  <pre><code>select distinct substring(r.promotion_code,5,CHAR_LENGTH(r.promotion_code)- 4) promotion_code, 
  numeric_code, 
  tier, 
  issue_date, 
  expire_date, 
  type, 
  reward, 
  r.status, 
  description, 
  if(r.status=2,if(t.issuance_date is null,'1900-01-01 00:00:00',t.issuance_date),'') redemption_date, 
  if(r.status=2,'Y','N') redeemed 
  from 
  rewards r join 
  status_enumeration s 
  on r.status = s.keyvalue 
  left join 
  trips t 
  on (r.promotion_code=t.promotion_code 
  or substring(r.promotion_code,5,CHAR_LENGTH(r.promotion_code)-4)=t.promotion_code) 
  where r.member_id=164631400 
  and table_name='rewards' order by issue_date desc;
  
  
  +----+-------------+-------+------+-------------------------------------------------------------+--------------------------+---------+---------------------------------+---------+--------------------------------------------------+
  | id | select_type | table | type | possible_keys                                               | key                      | key_len | ref                             | rows    | Extra                                            |
  +----+-------------+-------+------+-------------------------------------------------------------+--------------------------+---------+---------------------------------+---------+--------------------------------------------------+
  |  1 | SIMPLE      | s     | ALL  | idx_keyvalue                                                | NULL                     | NULL    | NULL                            |      41 | Using where; Using temporary; Using filesort     |
  |  1 | SIMPLE      | r     | ref  | NCIDX_type_reward_memberID_issDate,rewards$member_id$status | rewards$member_id$status | 13      | const,CUST_GHPROD_DB.s.keyvalue |       1 | NULL                                             |
  |  1 | SIMPLE      | t     | ALL  | idx_promotion_code                                          | NULL                     | NULL    | NULL                            | 3192871 | Range checked for each record (index map: 0x400) |
  +----+-------------+-------+------+-------------------------------------------------------------+--------------------------+---------+---------------------------------+---------+--------------------------------------------------+
  </code></pre>
  
  <p>Is there a better way to write this query?</p>
  
  <p>My table rewards, status_enumeration and trips are having following DDLs.</p>
  
  <pre><code>CREATE TABLE `rewards` (
    `rewards_id` int(11) NOT NULL AUTO_INCREMENT,
    `member_id` bigint(20) DEFAULT NULL,
    `promotion_code` varchar(50) DEFAULT NULL,
    `numeric_code` int(11) NOT NULL,
    `tier` varchar(2) NOT NULL,
    `type` varchar(2) NOT NULL,
    `reward` varchar(2) NOT NULL,
    `status` int(11) NOT NULL,
    `reward_source_type` char(1) DEFAULT 'B',
    `description` varchar(100) DEFAULT NULL,
    `issue_date` datetime NOT NULL,
    `expire_date` datetime NOT NULL,
    `RowGUID` varchar(64) DEFAULT NULL,
    `created_date` datetime DEFAULT CURRENT_TIMESTAMP,
    `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`rewards_id`),
    KEY `NCIDX_status_expireDate_rewardID` (`rewards_id`,`member_id`,`status`,`expire_date`),
    KEY `NCIDX_status_rewardsID_promCode` (`rewards_id`,`promotion_code`,`status`),
    KEY `NCIDX_type_desc_memberID_issDate` (`rewards_id`,`member_id`,`type`,`description`,`issue_date`,`status`,`expire_date`),
    KEY `NCIDX_type_reward_memberID_issDate` (`member_id`,`issue_date`,`type`,`reward`),
    KEY `rewards$member_id$status` (`member_id`,`status`),
    KEY `idx_promotion_code_rewards` (`promotion_code`,`status`)
  ) ENGINE=InnoDB AUTO_INCREMENT=15253716 DEFAULT CHARSET=latin1
  
  
  CREATE TABLE `status_enumeration` (
    `table_name` varchar(50) NOT NULL,
    `field_name` varchar(50) NOT NULL,
    `keyvalue` int(11) NOT NULL,
    `datavalue` varchar(50) NOT NULL,
    `RowGUID` varchar(64) DEFAULT NULL,
    KEY `idx_keyvalue` (`keyvalue`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  
  
      CREATE TABLE `trips` (
    `trip_number` int(11) NOT NULL AUTO_INCREMENT,
    `member_id` bigint(20) DEFAULT NULL,
    `confirmation_number` varchar(50) NOT NULL,
    `departure_city` varchar(100) NOT NULL DEFAULT '',
    `departure_state` varchar(2) NOT NULL DEFAULT '',
    `arrival_city` varchar(100) NOT NULL DEFAULT '',
    `arrival_state` varchar(2) NOT NULL DEFAULT '',
    `issuance_date` datetime NOT NULL,
    `departure_date` datetime NOT NULL,
    `arrival_date` datetime NOT NULL,
    `number_of_passengers` tinyint(3) unsigned NOT NULL DEFAULT '0',
    `fare_type` varchar(50) NOT NULL DEFAULT '',
    `mileage` int(11) NOT NULL DEFAULT '0',
    `status` tinyint(3) unsigned NOT NULL DEFAULT '0',
    `credits` int(11) NOT NULL DEFAULT '0',
    `promotion_code` varchar(50) NOT NULL DEFAULT '',
    `reference_id` varchar(50) NOT NULL DEFAULT '',
    `purchase_price` decimal(19,4) DEFAULT NULL,
    `RowGUID` varchar(64) DEFAULT NULL,
    `reward_points` decimal(10,0) NOT NULL DEFAULT '1',
    `created_date` datetime DEFAULT CURRENT_TIMESTAMP,
    `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`trip_number`),
    KEY `NCIDX_issuanceDate_memberID` (`member_id`,`issuance_date`),
    KEY `NCIDX_refID_tripNum_status` (`trip_number`,`status`,`reference_id`),
    KEY `NCIDX_status_tripNum_status_memberID_depGate_promCode` (`trip_number`,`member_id`,`departure_date`,`promotion_code`,`status`),
    KEY `trips$issuance_date` (`issuance_date`),
    KEY `trips$member_id$reference_id$arrival_city$departure_city$issua1` (`member_id`,`reference_id`,`arrival_city`,`departure_city`,`issuance_date`),
    KEY `trips$promCode$Status` (`status`,`promotion_code`),
    KEY `reference_id` (`reference_id`),
    KEY `member_id` (`member_id`,`status`,`departure_date`),
    KEY `reference_id_2` (`reference_id`),
    KEY `idx_promotion_code` (`promotion_code`)
  ) ENGINE=InnoDB AUTO_INCREMENT=32406357 DEFAULT CHARSET=latin1
  </code></pre>