Re: greatest-n-per-group performace for different data types

[Thomas Wiedmann]

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.