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

[Rick James]

Quote

I added the index (idx_locid) and the query time went down even further.

tells me nothing. What field(s) are in that index? Apparently INDEX(customerID)?

To "explain" the EXPLAIN:

id 	select_type 	table 	        type 	possible_keys 	           key 	                key_len 	ref 	rows 	Extra
Step 2:
1 	PRIMARY 	<derived2> 	ALL 	NULL 	                   NULL 	        NULL 	        NULL 	22361 	Using temporary; Using filesort
Step 3:
1 	PRIMARY 	customers 	ref 	idx_emid_custid,idx_locid   idx_emid_custid 	4 	        const,C.customerID 	2 	Using index
Step 1:
2 	DERIVED 	customers 	index 	NULL 	                   idx_locid 	        3 	        NULL 	137594 	Using index

Step 1: Use just an "index" (the new one, idx_locid) to do the inner query. It is a "index scan" of about 137594 rows. This creates a temp table, "derived2" ("2 DERIVED").

Step 2: For each of the rows ("ALL", "NULL") from Step 1, do the following...

Step 3: Look up rows, one at a time, using index "idx_dmid_custid" (emotionID, customerID), via a "const" (WHERE emotionID = 0) and C.customerID. Estimate finding "2" rows on each such probe. (UNIQUE would have let it find only 1 row, but this is not a big deal.) Then, since you only needed emotionID and customerID, it could do the work entirely in the index ("Using index").

"Using temporary; Using filesort" applies to the query as a whole, not necessarily to the line where it is found. It probably refers to the temp table needed by the subquery. No, you cannot get rid of it.

That is likely to be as fast as you can make that query.