MySQL Forums
Forum List  »  Optimizer & Parser

Simple 1-table query taking WAY to long.....
Posted by: Jason Moore
Date: January 23, 2012 02:31AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Simple 1-table query taking WAY to long.....
1426
January 23, 2012 02:31AM


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.