Re: Query with GROUP BY not using Index

[Øystein Grøvlen]

Imran Akbar Wrote:
-------------------------------------------------------
> Hi Øystein,
> thanks for the suggestion. The text field is
> 256 characters long, so the index of size 255
> shouldn't be a prefix, should it?

As long as 255 is less than 256, I would think it is a prefix.

> I tried to
> re-create the indexes without an explicit size, as
> you suggested:
>
> ALTER TABLE `epi_iv` DROP INDEX `wafer_name`, ADD
> INDEX `wafer_name` (`wafer_name`)
>
> ALTER TABLE `epi_iv` DROP INDEX `wafer_name_date`,
> ADD INDEX `wafer_name_date` (`wafer_name`,`date`)
>
> but the wafer_name indexes still have size 255,
> and the query hasn't improved its speed as it's
> still not using the composite index.
>
> Any other ideas?

There might be other issues, but the following show that creating it without a size is a prerequisite for using the index in this case:

mysql> create table t (i int primary key, s varchar(256), d datetime, key s (s(255)), key sd (s(255), d));
Query OK, 0 rows affected (0.17 sec)

mysql> insert into t values (1, 's1', current_time);
Query OK, 1 row affected, 1 warning (0.06 sec)

mysql> insert into t values (2, 's1', current_time);
Query OK, 1 row affected, 1 warning (0.07 sec)

mysql> explain select s from t group by s order by d desc\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using temporary; Using filesort
1 row in set (0.00 sec)

mysql> explain select s from t group by s\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using temporary; Using filesort
1 row in set (0.00 sec)

mysql> drop index s on t;
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> drop index sd on t;
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index s on t(s);
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index sd on t(s, d);
Query OK, 0 rows affected (0.72 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select s from t group by s\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: s
key_len: 259
ref: NULL
rows: 2
Extra: Using index
1 row in set (0.00 sec)

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway