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.


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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.