MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query with GROUP BY not using Index
Posted by: Imran Akbar
Date: August 24, 2012 12:41PM

Thanks James!

I removed the ambiguity by using GROUP BY in a sub-query and ORDER BY outside of it, which correctly returns the results in .07 seconds:

SELECT t1.wafer_name, t1.date FROM (SELECT wafer_name, MIN(date) as date FROM epi_flash_meas GROUP BY wafer_name) t1 JOIN epi_wafer t2 ON (t1.wafer_name=t2.wafer_name) ORDER BY t1.date DESC LIMIT 0,30

If I switch the order around, I get incorrect results returned in 7.5 seconds:
SELECT t1.wafer_name, MIN(t1.date) FROM (SELECT wafer_name, date FROM epi_flash_meas ORDER BY date DESC) t1 JOIN epi_wafer t2 ON (t1.wafer_name=t2.wafer_name) GROUP BY t1.wafer_name LIMIT 0,30

Which I guess makes sense, because it seems logical that I'll lose any ordering if I do a GROUP BY after the order. Is this the right reason the second query is incorrectly ordering results?

The output of the EXPLAINs for each statement:
first query:
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2253
Extra: Using filesort
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: t2
type: eq_ref
possible_keys: wafer_name
key: wafer_name
key_len: 767
ref: t1.wafer_name
rows: 1
Extra: Using index
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: epi_flash_meas
type: range
possible_keys: NULL
key: wafer_name_date
key_len: 776
ref: NULL
rows: 2320
Extra: Using index for group-by

second query:
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 405284
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: t2
type: eq_ref
possible_keys: wafer_name
key: wafer_name
key_len: 767
ref: t1.wafer_name
rows: 1
Extra: Using index
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: epi_flash_meas
type: index
possible_keys: NULL
key: date
key_len: 9
ref: NULL
rows: 433783
Extra:

thanks,
imran

Options: ReplyQuote


Subject
Views
Written By
Posted
2745
August 14, 2012 01:06PM
Re: Query with GROUP BY not using Index
1201
August 24, 2012 12:41PM


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.