Simple 1-table query taking WAY to long.....
I have been trying to improve the performance of my application by adding indexes(indices?) where appropriate. Some of my attempts have worked, but there are a few that are still causing me trouble. I have included the simplest "problem" query I could find. I'm hoping someone has the time to point out what simplistic thing I have screwed up. It so, it would be greatly appreciated.
I am running MySQL 5.1.41 on Ubuntu 10.04 32-bit.
The slow-query log reports the following:
--------------
# Query_time: 65.707585 Lock_time: 0.000059 Rows_sent: 1 Rows_examined: 249934
SET timestamp=1327303938;
SELECT `email_reports`.* FROM `email_reports` WHERE (`email_reports`.feed_id = 110) ORDER BY email_reports.sent_timestamp DESC LIMIT 1;
--------------
A DESCRIBE of this query yields the following results:
--------------------
id: '1',
select_type: 'SIMPLE',
table: 'email_reports',
type: 'index',
possible_keys: 'another_index,index_email_reports_group_by_feed_and_date,index_email_reports_sort_by_timestamp_per_feed,fk_email_reports_1,index_slow_query_1',
key: 'index_email_reports_on_sent_timestamp',
key_len: '9',
ref: NULL,
rows: '49054',
Extra: 'Using where'
--------------------
There are currently 311544 rows currently in the table, but the volume in production is expected to get MUCH higher.
The table is defined as follows:
--------------
CREATE TABLE `email_reports` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`feed_id` int(11) NOT NULL,
`email_data` blob,
`failure_message` text COLLATE utf8_unicode_ci,
`parsed` tinyint(1) NOT NULL DEFAULT '0',
`has_positions` tinyint(1) DEFAULT NULL,
`indicates_on` tinyint(1) DEFAULT NULL,
`removed` tinyint(1) NOT NULL DEFAULT '0',
`sent_timestamp` datetime DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`sent_date` date DEFAULT NULL,
`report_type` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`),
KEY `index_email_reports_on_parsed` (`parsed`),
KEY `index_email_reports_on_sent_timestamp` (`sent_timestamp`) USING BTREE,
KEY `another_index` (`feed_id`,`sent_date`,`parsed`) USING BTREE,
KEY `index_email_reports_group_by_feed_and_date` (`feed_id`,`sent_date`),
KEY `index_email_reports_sort_by_timestamp_per_feed` (`feed_id`,`sent_timestamp`),
KEY `fk_email_reports_1` (`feed_id`),
KEY `index_slow_query_1` (`feed_id`,`parsed`,`sent_timestamp`) USING BTREE,
CONSTRAINT `fk_email_reports_1` FOREIGN KEY (`feed_id`) REFERENCES `feeds` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=311545 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
--------------
Thanks again for your time, and any help you are able to give.
-Jason