MySQL Forums
Forum List  »  Performance

Best index combination
Posted by: sudheer kumar
Date: June 13, 2011 12:24AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Best index combination
2298
June 13, 2011 12:24AM
712
June 14, 2011 06:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.