Applying an Index to a Blob/Longtext field

Question

I am trying to create an index on a particular text string using Hibernate annotations. What I am currently doing is:

@Index(name="guid_index")
@Column(length=1400)
private String myGUID;

However, since the length of the column is 1400, this maps to SQL using the longtext type. hbm2ddl is able to create the table without mishap, but when it tries to create the index, I get:

Unsuccessful: create index guid_index on table_name (myguid);
BLOB/TEXT column 'myguid' used in key specification without a key length

How can I create an index on this column using Hibernate?

Answer 1

You have to specify a length of index. Maximum length depends on storage engine, but usually it's not more than 1000 bytes, if charset is utf-8, you have to divide maximum lengh by 3.

create index guid_index on table_name (myguid (1000) );

It's good enough for WHERE field LIKE 'prefix%' and WHERE field = 'text' conditions.

Answer 2

I initially thought that HHH-4389 was what you're looking for but your case seems to be a different issue: Hibernate is simply not generating the expected DDL statement. I would open a new Jira issue.