MySQL Forums
Forum List  »  InnoDB

INNO DB performance problems with temporary tables
Posted by: Koen B
Date: September 23, 2009 07:34AM

Hi Folk,

Recently, i've been working with InnoDB since MyISAM had to many limitations.
Now i have a strange situation where a query runs very slow when using a range scan.

My situation: I've got 2 tables: one containing feed definations named feedreferences with approximately 1000 items in it.
The second table contains the items for the feed, this table contains around the 1 000 000 (million) items.
Both tables have a field named Service which is an integer with an Index on it. Also, feedservicereferences has a Field called Uri which contains a varchar(4098) with a unique index on it.

Now the problem: When selecting items for more then 1 feedservice, the time it takes is always +10 seconds while selecting for only one feedservice takes just a few miliseconds.

The query for selecting items from one feed is:

SELECT * FROM `ici`.`feedservicereferences` r
JOIN feeditems i ON r.Service = i.Service
WHERE r.Uri = 'http://telegraaf.nl/rss';
ORDER BY i.ID DESC
LIMIT 0, 10

AND as you may quess, the query for selecting items from mulitple feeds is:

SELECT * FROM `ici`.`feedservicereferences` r
JOIN feeditems i ON r.Service = i.Service
WHERE r.Uri IN ('http://telegraaf.nl/rss';, 'http://925.nl/rss';)
ORDER BY i.ID DESC
LIMIT 0, 10

The time difference is just absurd, so one option that might come to mind is that when working with multiple feeds, all time is consumed by creating a temporary table but this is unlikely as the EXPLAIN command tells us:
(1 feed)
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE r const Unique_Uri,Index_Service Unique_Uri 752 const 1 (null)
1 SIMPLE i ref Index_Service Index_Service 4 const 7120 Using where

(multiple feeds)
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE r range Unique_Uri,Index_Service Unique_Uri 752 2 Using where; Using temporary; Using filesort
1 SIMPLE i ref Index_Service Index_Service 4 ici.r.Service 1755 (null)

I'm a bit lost here, clearly the amount of selected items are next to nothing, currently i made a workaround by using a select with a subquery which performs a single select for each feed and combines them using a union all but ofcourse this is plain dirty and theoratically performance killing...

Can someone explain what the frack is going on?

Thanks in advance!



Edited 1 time(s). Last edit at 09/23/2009 07:43AM by Koen B.

Options: ReplyQuote


Subject
Views
Written By
Posted
INNO DB performance problems with temporary tables
3526
September 23, 2009 07:34AM


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.