CODEWITHSUNDEEP
mysqlhibernateormindexingmysql-error-1170
VeeArr
VeeArr

Reputation: 6178

Applying an Index to a Blob/Longtext field

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?

Upvotes: 2

Views: 7530

Answers (2)

Pascal Thivent
Pascal Thivent

Reputation: 570325

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.

Upvotes: 3

Naktibalda
Naktibalda

Reputation: 14100

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.

Upvotes: 2

Related Questions