In which order(table column or query) composite index should made?

Question

I have a sample table like -

CREATE TABLE `cdr` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dataPacketDownLink` bigint(20) DEFAULT NULL,
  `dataPacketUpLink` bigint(20) DEFAULT NULL,
  `dataPlanEndTime` datetime DEFAULT NULL,
  `dataPlanStartTime` datetime DEFAULT NULL,
  `dataVolumeDownLink` bigint(20) DEFAULT NULL,
  `dataVolumeUpLink` bigint(20) DEFAULT NULL,
  `dataplan` varchar(255) DEFAULT NULL,
  `dataplanType` varchar(255) DEFAULT NULL,
  `createdOn` datetime DEFAULT NULL,
  `deviceName` varchar(500) DEFAULT NULL,
  `duration` int(11) NOT NULL,
  `effectiveDuration` int(11) NOT NULL,
  `hour` tinyint(4) DEFAULT NULL,
  `eventDate` datetime DEFAULT NULL,
  `msisdn` bigint(20) DEFAULT NULL,
  `quarter` tinyint(4) DEFAULT NULL,
  `validDays` int(11) DEFAULT NULL,
  `dataLeft` bigint(20) DEFAULT NULL,
  `completedOn` datetime DEFAULT NULL,
  `evedate` date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`id`,`evedate`),
  KEY `evedate_index` (`evedate`),
  KEY `eve_hour_index` (`evedate`,`hour`),
  KEY `eve_msisdn_index` (`evedate`,`msisdn`)
) ENGINE=MyISAM AUTO_INCREMENT=259341694 DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE  COLUMNS(evedate)
(PARTITION `START` VALUES LESS THAN ('2013-09-01') ENGINE = MyISAM,
 PARTITION p01 VALUES LESS THAN ('2013-09-08') ENGINE = MyISAM,
 PARTITION p02 VALUES LESS THAN ('2013-09-15') ENGINE = MyISAM,
 PARTITION p03 VALUES LESS THAN ('2013-09-22') ENGINE = MyISAM,
 PARTITION p04 VALUES LESS THAN ('2013-09-29') ENGINE = MyISAM,
 PARTITION p05 VALUES LESS THAN ('2013-10-06') ENGINE = MyISAM,
 PARTITION p06 VALUES LESS THAN ('2013-10-12') ENGINE = MyISAM,
 PARTITION p07 VALUES LESS THAN ('2013-10-19') ENGINE = MyISAM,
 PARTITION p08 VALUES LESS THAN ('2013-10-25') ENGINE = MyISAM,
 PARTITION p09 VALUES LESS THAN ('2013-10-31') ENGINE = MyISAM,
 PARTITION p10 VALUES LESS THAN (MAXVALUE) ENGINE = MyISAM) */

Now look at the order of index eve_hour_index(evedate,hour) and eve_msisdn_index(evedate,msisdn). But I declared evedate in the last.

I have read somewhere that order in composite index is important. So which order? order in query or order in table?

Do I have to re-write this compound index as eve_hour_index(hour,evedate) and eve_msisdn_index(msisdn,evedate)?

Or the first one is correct?

Thank you.

Answer 1

In a nutshell, an index object for a column contains the values of that column sorted so that a given value can quickly be found. For a composite index, it is similar, but the values for the columns in the composite index are combined and sorted so any queries involving all of the columns in the composite index are much faster.

A note is that if you have a composite index on columns A and B (specified in that order), it will greatly improve speed on queries that have conditions on both A and B, could help speed on queries that just have conditions on A, and probably won't help speed on queries that just have conditions on B.