CODEWITHSUNDEEP
mysqlindexing
Robin-Hoodie
Robin-Hoodie

Reputation: 4974

Disable auto indexing of mysql

I understand MySQL is automatically placing an index on every table's primary and foreign keys.

However, I would like to personally create my own indices on the foreign keys as I want to execute a query with hibernate showing the difference in time when I execute it with and without indices.

Is there any option in MySQL Workbench to disable it's auto indexing feature?

Upvotes: 0

Views: 1696

Answers (2)

Barmar
Barmar

Reputation: 780663

No, these indexes are always created. Otherwise, every UPDATE or INSERT that modifies these columns would have to perform a full table scan, to ensure that the primary key is unique and the foreign key has a valid reference.

Regarding foreign keys, the documentation says:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

No you cannot disable the auto index creation of index on tables. This is a inbuilt feature which is added in MySql.

However if you want you can drop the index like this:

DROP INDEX index_name ON tbl_name

and then create it again.

From InnoDB and FOREIGN KEY Constraints

"InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously."

Upvotes: 3

Related Questions