Re: Query with GROUP BY not using Index

[Imran Akbar]

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