MySQL query for top N records of grouped records

Question

I am new to MySql and databases in general. I have a query that I created together using snippets from online resources and tracking and errors. This is very slow (27 seconds), and I assume that it can be optimized. Maybe someone can help me with this.

This is the data structure for my mySQL database. Version 5.1.51-0

|- purchaseID -|- customerID -|- emotionID -|- customerCountryCode -|- customerContinentCode-|
|     1        |     2345     |     0       |        US             |            NA          |
|     2        |     2345     |     3       |        US             |            NA          |
|     3        |     4456     |     0       |        UK             |            EU          |
|     3        |     4456     |     5       |        UK             |            EU          |
|     4        |     4456     |     2       |        UK             |            EU          |
|     5        |     4456     |     2       |        UK             |            EU          |
|     6        |     1234     |     0       |        US             |            NA          |
|     7        |     6678     |     0       |        US             |            NA          |
|     8        |     9900     |     0       |        US             |            NA          |
|     9        |     3334     |     0       |        US             |            NA          |    
|     10       |     3334     |     4       |        US             |            NA          |

The database is used to save all purchases made. For each purchase customerID, the country and continent from which it comes are saved. The client also has the opportunity to evaluate his purchase from a set of 6 emotions. (happy, disappointed, ...) The emotions he chooses are saved as emotionID.

, , 6 emotionID . , emotionID = 0, , :

|- customerID -|- emotionPercent -|
|     1234     |        100       |     
|     6678     |        100       |     
|     9900     |        100       | 
|     2345     |        50        |     
|     3334     |        50        | 
|     4456     |        25        |    

:

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 customerID) C 

ON customers.customerID = C.customerID 
WHERE customers.emotionID = 0 
GROUP BY customers.customerID 
ORDER BY emotionPercent DESC 
LIMIT 0,6

, . , - , .

: 140 000 , 27 . ? SQL Server ?

, , : (0,4 ), (27 ):

SELECT customers.customerCountryCode, Count( customers.emotionID ) / C.totalPeople * 100.0 AS emotionPercent 
FROM `customers` 
INNER JOIN 

    (SELECT customers.customerCountryCode, Count( customers.emotionID ) AS totalPeople
    FROM `customers` 
    GROUP BY customerCountryCode) C 

ON customers.customerCountryCode = C.customerCountryCode 
WHERE customers.emotionID = 0 
GROUP BY customers.customerCountryCode 
ORDER BY emotionPercent DESC 
LIMIT 0,6

GROUP BY INNER Query customerID, . customerID, . ?

customerCountryCode varchar(2). customerID int(11). ? ? customerID 8 .

! !

Answer 1

, , , , , , IMHO, , . , . memcache php ehcache j2ee .

Answer 2

, . , (.. ). , SQL, .

• 6, .
• 6 WHERE custumerID IN (id1, id2, id3, etc)

, , , ( ).

Answer 3

!

mySQL :

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

27 0,1 .;)

,

(SELECT customers.customerCountryCode, Count( * ) AS totalPeople
    FROM `customers` 
    GROUP BY customerCountryCode) C