MySQL Forums
Forum List  »  Optimizer & Parser

Query with GROUP BY not using Index
Posted by: Imran Akbar
Date: August 14, 2012 01:06PM

The following query is taking 3.8 seconds to run:
SELECT wafer_name FROM epi_iv GROUP BY wafer_name ORDER BY date DESC;

EXPLAIN SELECT wafer_name FROM epi_iv GROUP BY wafer_name ORDER BY date DESC\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: epi_iv
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 496613
Extra: Using temporary; Using filesort

SHOW CREATE TABLE's output is really long, but the relevant parts are:
`wafer_name` varchar(256) collate utf8_unicode_ci NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`iv_id`),
KEY `wafer_id` (`wafer_id`),
KEY `wafer_name` (`wafer_name`(255)),
KEY `wafer_name_date` (`wafer_name`(255),`date`)
) ENGINE=InnoDB AUTO_INCREMENT=514387 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

If I simplify the query by removing the ORDER BY, it still takes 3 seconds:
SELECT wafer_name FROM epi_iv GROUP BY wafer_name;
I know this query can run in ~.05 seconds when it uses the index because it does so on another table with about the same number of rows and the same composite index.

The cardinality of the wafer_name_date composite index is:
3463 for wafer_name and 126435 for the date.

Any ideas?

thanks

Options: ReplyQuote


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


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.