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

[Martin Schulz]

Thanks for your help!

I changed the inner query to COUNT(*):

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

The result of EXPLAIN SELECT is the same. Speed seems to be the same.

I'm sorry, but I don't get what you mean with INDEX(...). I already created two indices like Thomas wrote.

ALTER TABLE customers
  ADD KEY idx_country_emid (customerCountryCode, emotionID),
  ADD KEY idx_emid_custid (emotionID, customerID);

But I assume, you mean something else?!

Thanks again!