Question 88771 in Mysql Question posted in
The official MySQL documentation can be found here.

How to create an index for a 'TEXT' column in a MySQL database by means of JPA/Hibernate

I need to create an index for a ‘TEXT’ column in a MySQL database. I can’t do it using this annotation

@Table(name = "table1", indexes = @Index(name = "ix_table1_column1", columnList = "column1"))

as MySQL can only index the first N characters of the ‘TEXT’ column, so I get this kind of exception:

BLOB/TEXT column 'column1' used in key specification without a key length

(obviously I don’t get this exception if I change the type of the column from ‘TEXT’ to ‘VARCHAR(255)’)

I’ve tried to create an index via a custom HQL query by adding a method to the repository:

@Query("create index ix_table1_column1 on Table1Entity(column1(255))") 
void createIndex();

but with no success, as I get this kind of exceptions:

unexpected token: create near line 1, column 1 [create index create index ix_table1_column1 on Table1Entity(column1(255))]

(looks like there’s no ‘create index’ command in HQL)

However, with the ‘TEXT’ type of column1, I’m able to create an index via this statement in the database where I just add a character limit to the name of the column:

create index ix_table1_column1 on schema1.table1(column1(255));

Is there a way to do this via an annotation of via an HQL custom query?

Tags:

2

Answers


  1. 0

    Just place the command:

    create index ix_table1_column1 on schema1.table1(column1(255))

    in a .sql file, and then use the standard JPA property jakarta.persistence.schema-generation.create-script-source to tell Hibernate where to find it.

    Login or Signup to reply.
  2. 0

    Let’s dig deeper into what you plan to do with that index.

    • Exact match on long Text? This seems unlikely application.
    • Be happy with checking just the beginning of the text? Well, the "prefix" index may or may not help performance. Example: WHERE col LIKE 'Start%'
    • Looking for something in the middle of the Text? Well, col LIKE '%abc%' will not use the index and will be slow.
    • Hoping to search for a "word" in the Text? That begs for a FULLTEXT index and the construct SELECT ... WHERE MATCH(col) AGAINST("word" IN BOOLEAN MODE) — This is fast and efficient, but has limitations.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search