MySQL Forums
Forum List  »  Optimizer & Parser

Re: Query with GROUP BY not using Index
Posted by: Øystein Grøvlen
Date: August 17, 2012 02:58AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
2747
August 14, 2012 01:06PM
Re: Query with GROUP BY not using Index
1523
August 17, 2012 02:58AM


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.