My mysql DB is pretty big (~21M records, ~15gb).
I use JPA with Hibernate implementation.

I'm executing simple query with JPQL which is translated to such SQL query:

select * from tranzakcje where match(dane_nadawcy) against("+name + surname" in boolean mode) order by data_waluty

It takes infinity to execute. Same query executed with a mysql client work fine. General log for the Hibernate query shows this:

090623 21:25:21 68 Query SHOW COLLATION
68 Query SET NAMES cp1250
68 Query SET character_set_results = NULL
68 Query SET autocommit=1
68 Query SET sql_mode='STRICT_TRANS_TABLES'
68 Query SELECT @@session.tx_isolation
68 Query SHOW FULL TABLES FROM `ksiegowosc` LIKE 'adresaci'
68 Query SHOW FULL TABLES FROM `ksiegowosc` LIKE 'adresaci'
68 Query SHOW FULL COLUMNS FROM `adresaci` FROM `ksiegowosc` LIKE '%'
68 Query SHOW CREATE TABLE `ksiegowosc`.`adresaci`
68 Query SHOW INDEX FROM `adresaci` FROM `ksiegowosc`
68 Query SHOW FULL TABLES FROM `ksiegowosc` LIKE 'pliki'
68 Query SHOW FULL TABLES FROM `ksiegowosc` LIKE 'pliki'
68 Query SHOW FULL COLUMNS FROM `pliki` FROM `ksiegowosc` LIKE '%'
68 Query SHOW CREATE TABLE `ksiegowosc`.`pliki`
68 Query SHOW INDEX FROM `pliki` FROM `ksiegowosc`
68 Query SHOW FULL TABLES FROM `ksiegowosc` LIKE 'tranzakcje'
68 Query SHOW FULL TABLES FROM `ksiegowosc` LIKE 'tranzakcje'
68 Query SHOW FULL COLUMNS FROM `tranzakcje` FROM `ksiegowosc` LIKE '%'
68 Query SHOW CREATE TABLE `ksiegowosc`.`tranzakcje`
68 Query SHOW INDEX FROM `tranzakcje` FROM `ksiegowosc`
68 Query alter table tranzakcje add index FKA4B86F258595EEA3 (id_pliku), add constraint FKA4B86F258595EEA3 foreign key (id_pliku) references pliki (id_pliku)

The last statement takes hours to execute (obviously because of large DB size). I don't know why it tries to add index, because indices are already added (am I missing something Hibernate specific?):

+------------+------------+----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| tranzakcje | 0 | PRIMARY | 1 | id_tranzakcji | A | 0 | NULL | NULL | | BTREE | |
| tranzakcje | 1 | id_pliku_trans_index | 1 | id_pliku | A | NULL | NULL | NULL | | BTREE | |
| tranzakcje | 1 | data_waluty_index | 1 | data_waluty | A | NULL | NULL | NULL | | BTREE | |
| tranzakcje | 1 | kwota_index | 1 | kwota | A | NULL | NULL | NULL | | BTREE | |
| tranzakcje | 1 | FKA4B86F258595EEA3 | 1 | id_pliku | A | NULL | NULL | NULL | | BTREE | |
| tranzakcje | 1 | dane_nadawcy_index | 1 | dane_nadawcy | NULL | NULL | NULL | NULL | YES | FULLTEXT | |
| tranzakcje | 1 | tresc_przelewu_index | 1 | tresc_przelewu | NULL | NULL | NULL | NULL | YES | FULLTEXT | |
+------------+------------+----------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+

While executing alter table tranzakcje add index FKA4B86F258595EEA3 (id_pliku), add constraint FKA4B86F258595EEA3 foreign key (id_pliku) references pliki (id_pliku) mysqladmin processlist shows this:


+----+------------+----------------+------------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------------+----------------+------------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| 5 | ksiegowosc | localhost:2034 | ksiegowosc | Query | 10 | copy to tmp table | alter table tranzakcje add index FKA4B86F258595EEA3 (id_pliku), add constraint FKA4B86F258595EEA3 fo |
| 28 | malinowl | localhost | | Query | 0 | | show processlist |
+----+------------+----------------+------------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+

Why is this happening to me? Why Hibernate tries to add this index? Is there any way to tell Hibernate to omit it? Any comments highly appreciated.

It seems that turning off schema update with <property name="hibernate.hbm2ddl.auto" value="none"/> helped. But I don't know why Hibernate tried to add index that already existed in database.