Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a query like this: 

SELECT *  
FROM `rss_item` 
WHERE `feed_id` = 1 and `timestamp` < 1350698635 
ORDER BY `rss_item`.`timestamp` ASC

MySQL: 5.1; table engine: innodb

Explain result: 

id  |  select_type  |  table     |  type   |  possible_keys  |  key        |  key_len |  ref     |  rows    |  Extra  
1   |  SIMPLE       |  rss_item  |  range  |  timestamp      |  timestamp  |  4       |  NULL    |  133534  |  Using where

Table schema: 

CREATE TABLE `rss_item2` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(1000) NOT NULL,
    `description` longtext NOT NULL,
    `url` varchar(200) NOT NULL,
    `url_crc` bigint(20) NOT NULL,
    `date` datetime NOT NULL,
    `timestamp` int(11) NOT NULL,
    `feed_id` int(11) NOT NULL,
    `image` varchar(250) NOT NULL,
    `goto` int(11) NOT NULL,
    `likes` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `timestamp` (`timestamp`,`feed_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2425930 DEFAULT CHARSET=utf8

This query with 400,000 rows takes 4 seconds. I added a composite index on feed_id and timestamp fields. How can I tune this query?

share|improve this question
1  
Hpw do you measure those 4 seconds? Pushing so much data through a network connection can be slow. A query plan would help anyway. – dezso Jan 15 at 12:06
1  
Yes, can you paste here your explain plan (EXPLAIN SELECT * FROM `rss_item...) we'll can see if MySQL use your index or not. I suspect MySQL take time on the "order by" clause. – mfouilleul Jan 15 at 12:18
@mfouilleul i add explain result in question, thank. – vahid chakoshy Jan 15 at 12:25
Ok, can you paste an "SHOW CREATE TABLE rss_item" ? – mfouilleul Jan 15 at 12:31
1  
Why is the mismatch between the plan and the text (133534 vs 400000 rows)? – dezso Jan 15 at 12:57
show 2 more comments

1 Answer

up vote -1 down vote

I dont know about feed_id but your timestamp column by definition have probably many differents values. I will advise you to try to create a simple index on timestamp column.

Do you have "disparate" values too on feed_id column?

For me you should take your attention on the timestamp column (WHERE + ORDER BY). Maybe it'll not a index problem, maybe your MySQL instance have some difficulties to sort the result (bad temp table management) so you can try without ORDER BY clause to see its impact.

Let me know.

Max.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.