group by left(col, x) text column – possible to index?

All we need is an easy explanation of the problem, so here it is.

I’m looking for a possible index for the following:

select left(name,10), count(*)
from tab
group by left(name,10)

This is the table config:

create table tab(
id int(11),
name text,
primary key(id),
key name(name(10)))

The optimizer does not uses the index for the select. Any idea if that possible in MySQL?
In Oracle for example I can create index on functions, which is not supported in MySQL.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You may want to try something like this:

create table x 
(
  y varchar (25),
  z varchar (10) generated always as (left(y, 19)) stored, -- also virtual
  key (z) 
);

Or you could name the index as follows (see here):

CREATE INDEX x_z_ix ON x (z);

I find that giving explicit names to indexes is preferable – it gives more meaningful error messages.

Just because there is an index doesn’t mean that the optimiser will actually use it! The table might be too small, too many nulls, skew in values &c.

I’m not exactly sure what your own create table syntax is actually doing – could you post the output of show create table tab\G.

Method 2

TEXT has some drawbacks relative to VARCHAR(...). You have found one.

If name is a typical "name" of something, then it is probably never bigger than 100 characters, perhaps never bigger than 40. I recommend using a suitable VARCHAR instead of TEXT, whenever practical for your application.

The "prefix" indexing is a kludge to let you have some flavor of indexing on TEXT columns. However, as you are finding, it has some quirky limitations. Yes there is a "generated column" workaround; that, too, is a kludge.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply