MySQL Forums
Forum List  »  Performance

Re: greatest-n-per-group performace for different data types
Posted by: Martin Schulz
Date: December 13, 2010 08:17AM

Second query:

    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
        ORDER BY NULL) C 
    
    ON customers.customerCountryCode = C.customerCountryCode 
    WHERE customers.emotionID = 0 
    GROUP BY customers.customerCountryCode 
    ORDER BY emotionPercent DESC 
    LIMIT 0,6

result:

id 	select_type 	table 	        type 	possible_keys 	                  key 	                key_len 	ref 	rows 	Extra
1 	PRIMARY 	<derived2> 	ALL 	NULL 	                          NULL 	                NULL 	        NULL 	241 	Using temporary; Using filesort
1 	PRIMARY 	customers 	ref 	idx_country_emid,idx_emid_custid  idx_country_emid 	5 	        C.customerCountryCode,const 	103 	Using index
2 	DERIVED 	customers 	index 	NULL 	                          idx_country_emid 	5 	        NULL 	137594 	Using index

EDIT:

Just tried to re-run my queries. They are lightning fast by using the indices. Thank you sooo much. You really made my day.

Or did you even have some additional improvements in mind?



Edited 1 time(s). Last edit at 12/13/2010 08:23AM by Martin Schulz.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: greatest-n-per-group performace for different data types
987
December 13, 2010 08:17AM


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.