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

[Thomas Wiedmann]

Hi Martin,

it seems that your Query didn't match the EXPLAIN result. Where is the table "emoWeather"?
Please compute EXPLAIN with..

mysql>EXPLAIN SELECT ...

One hint...add some ".. ORDER BY NULL" to your Sub-Query. Look at this..

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

with kind regards,
Thomas