Best index combination
I am facing problem with index.
how to avoid file sorting for the following query using index?
SELECT * FROM app_notify_queue WHERE state=0 AND report<>'s' AND requested > 0 ORDER BY report_date LIMIT 1000
required information.
SHOW CREATE TABLE app_notify_queue
CREATE TABLE `app_notify_queue` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`esme_id` MEDIUMINT(4) NOT NULL,
`trans_id` VARCHAR(50) DEFAULT NULL,
`msg_id` VARCHAR(254) DEFAULT NULL,
`origin` VARCHAR(35) DEFAULT NULL,
`destination` VARCHAR(35) DEFAULT NULL,
`submit_date` CHAR(25) DEFAULT NULL,
`report` CHAR(1) DEFAULT NULL,
`report_date` CHAR(25) DEFAULT NULL,
`state` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`start_time` CHAR(25) DEFAULT NULL,
`end_time` CHAR(25) DEFAULT NULL,
`err_code` SMALLINT(5) NOT NULL DEFAULT '0',
`err_msg` VARCHAR(32) DEFAULT NULL,
`msg` VARCHAR(1024) DEFAULT NULL,
`retry_count` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
`requested` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
`r_esmeid` INT(11) DEFAULT NULL,
`service_id` VARCHAR(30) DEFAULT NULL,
`dlvr_referenceid` VARCHAR(128) DEFAULT '',
`corelation_id` VARCHAR(50) DEFAULT NULL COMMENT 'sub-appname from the application',
`ext_flag` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`extraparams` VARCHAR(1024) DEFAULT '',
`status_code` INT(5) DEFAULT '0',
`camp_code` TINYINT(1) DEFAULT '3',
`uscid_code` INT(6) DEFAULT '0',
`appid` INT(11) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `dlv_report_trans_id` (`trans_id`),
KEY `dlv_report_msg_id` (`msg_id`),
KEY `idx_submit_date` (`submit_date`),
KEY `dlv_destination` (`destination`),
KEY `dlv_referenceid` (`dlvr_referenceid`),
KEY `dlv_report_date` (`report_date`),
KEY `c_anq_1` (`state`,`submit_date`),
KEY `c_anq_2` (`state`,`report`,`requested`,`report_date`)
) ENGINE=INNODB AUTO_INCREMENT=7196622 DEFAULT CHARSET=latin1
EXPLAIN SELECT * FROM app_notify_queue WHERE state=0 AND report<>'s' AND requested > 0 ORDER BY report_date LIMIT 1000
id select_type TABLE TYPE possible_keys KEY key_len ref ROWS Extra
1 SIMPLE app_notify_queue RANGE c_anq_1,c_anq_2 c_anq_2 6 \N 11 USING WHERE; USING filesort
what is the best combination to use index for the above query?