MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query with GROUP BY not using Index
Posted by: Imran Akbar
Date: August 28, 2012 06:21PM

Thanks Øystein,
Indeed your simplified query and mine both return the same results. The simplified query, however, takes .8 seconds instead of .06 seconds. It appears to be because it's use of a temporary table, so I'm going to try to figure out how to fix that.

Here are the EXPLAIN outputs (the original query first):

mysql> EXPLAIN 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\G
*************************** 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: 10349
Extra: Using index for group-by
3 rows in set (0.06 sec)


mysql> EXPLAIN SELECT t1.wafer_name, MIN(t1.date) FROM epi_flash_meas t1 JOIN epi_wafer t2 USING (wafer_name) GROUP BY wafer_name ORDER BY t1.date DESC LIMIT 0,30\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index
possible_keys: wafer_name_date
key: date
key_len: 9
ref: NULL
rows: 416849
Extra: Using index; Using temporary
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
type: eq_ref
possible_keys: wafer_name
key: wafer_name
key_len: 767
ref: epi.t1.wafer_name
rows: 1
Extra: Using index
2 rows in set (0.00 sec)

thanks,
imran

Options: ReplyQuote


Subject
Views
Written By
Posted
2746
August 14, 2012 01:06PM
Re: Query with GROUP BY not using Index
1336
August 28, 2012 06:21PM


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.