Does Hibernate automatically create index?

8
votes

I have a table Term with a little more than 5 million rows. Each row consists of an ID and a "name". A search with "name" field takes nearly 35 seconds.

MariaDB [WordDS]> select * from Term where name="Google";
+---------+--------+
| id      | name   |
+---------+--------+
| 1092923 | Google |
+---------+--------+
1 row in set (35.35 sec)

The script from the table generated by Hibernate:

DROP TABLE IF EXISTS `Term`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Term` (
  `id` bigint(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

Java annotation:

@Id
@Column(unique=true)
private long id;

@NotNull
@Size(min = 1, max = 100)
private String name;

It's so slow to search on the field "name", so I guess there is no index. Does Hibernate automatically create index on id and name in this table? If not, how to let it create index for both?

2
mysqlhibernate

2 Answers

3
votes

Use the @Index annotation:

@Entity
@Table(name = "Term", indexes = {
    @Index(columnList = "name, id", name = "name_idx") })

Hibernate will create the index automatically when it creates the table at startup.

1
votes

The primary key is indexed automatically, for the name field, you have to add an @Index annotation:

@NotNull
@Size(min = 1, max = 100)
@Index(name = "idx_name")
private String name;