MySQL Forums
Forum List  »  InnoDB

Re: INNO DB performance problems with temporary tables
Posted by: Rick James
Date: September 24, 2009 09:39AM

IN does not always optimize well. UNION is a workaround

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
-->
( 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 )
UNION
( SELECT  *
    FROM  `ici`.`feedservicereferences` r
    JOIN  feeditems i ON r.Service = i.Service
    WHERE  = 'http://925.nl/rss';
    ORDER BY  i.ID DESC
    LIMIT  0, 10 )
ORDER BY ID DESC
LIMIT 0, 10;

You have some bulky stuff in the query, right? Then something like this would avoid hauling around the TEXT/BLOBs until the last minute. (Caveat: this might not work in your case, or it might not help.)

SELECT *
    FROM  `ici`.`feedservicereferences` r
    JOIN  feeditems i ON r.Service = i.Service
    JOIN
(
( SELECT  ID
    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 )
UNION
( SELECT  ID
    FROM  `ici`.`feedservicereferences` r
    JOIN  feeditems i ON r.Service = i.Service
    WHERE  = 'http://925.nl/rss';
    ORDER BY  i.ID DESC
    LIMIT  0, 10 )
ORDER BY ID DESC
LIMIT 0, 10
)
ORDER BY ID DESC
LIMIT 0, 10;
What's the diff? This hauls around 10 IDs (initially 20); IDs are small. Possibly it can do the work inside the indexes and not touch the bulky data. Then it reaches into the tables to get the bulky stuff only 10 times.
r should have INDEX(uri, Service)
i should have INDEX(Service, ID)

WARNING! Tomorrow, you are going to add
AND deleted = 0
That will bring performance back to "miserable"!
(I see this happening a lot.)

I'll bet the 0,10 is for pagination? Well, that leads to another performance nightmare.

To explain why the you got in trouble in the first place... Your WHERE clause filters in one table, then you ORDER BY using something in another table. The likely way for MySQL to perform that is to find ALL rows in the first table that meet the WHERE clause, put them into a temp table, then reach into the other table that to find ALL the rows based on the JOIN. Only then can it sort (ORDER BY) and deliver 10 (LIMIT). With SELECT *, that means ALL the TEXT fields, etc are carried in the temp table(s).

Another possible, but risky, way to optimize it... Start with the ORDER BY ID and reach into the other table to check to see if the row is needed. This tends to be so risky and costly that MySQL tends not to like it.

Of course, if you don't have the 'right' indexes, MySQL can't do either optimization. In the extreme case (no indexes), it does many table scans.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: INNO DB performance problems with temporary tables
1717
September 24, 2009 09:39AM


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.