MySQL Forums
Forum List  »  Performance

Re: greatest-n-per-group performace for different data types
Posted by: Thomas Wiedmann
Date: December 13, 2010 12:58PM

Hi Martin,

I'm not sure, but is it possible to switch the queries (outer / sub-query) liks this?

    SELECT customers.customerID, C.People / Count( customers.emotionID ) * 100.0 AS emotionPercent 
    FROM `customers` 
    INNER JOIN 
    
        (SELECT customers.customerID, Count(*) AS People
           FROM `customers` 
          WHERE customers.emotionID = 0         
          GROUP BY customers.customerID
          ORDER BY NULL) C 
    
    ON customers.customerID = C.customerID 
    GROUP BY customers.customerID 
    ORDER BY emotionPercent DESC 
    LIMIT 0,6

I assume, that the SUB-Query compute now smaler result-set than the wrapper-query.
Please check this out and give the EXPLAIN again.

Maybe your problem is solved in the meantime, don't know... is it?

with kind regards,
Thomas



Edited 1 time(s). Last edit at 12/13/2010 01:01PM by Thomas Wiedmann.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: greatest-n-per-group performace for different data types
928
December 13, 2010 12:58PM


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.