2

i am adding index to a very large table , around 25 M records and 60 GB of data. i am using MYSQL version 5.6 InnoDB , my statement is :

SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS = 0;
ALTER TABLE `my_table`
ADD   KEY `my_idx` (`site_id`,`name`);

and i am running it like this :

mysql --host=**.database.azure.com -u *** -p*** my_db < my_file.sql

but it take around 1 hour , the same time as without this statement , what am i missing ?

1
2

SET FOREIGN_KEY_CHECKS=0; and SET UNIQUE_CHECKS = 0; are meant for speeding up imports (typically INSERT statements) into tables where foreign keys and unique indexes already exist. They are not meant to speed up the creation of indexes.

2
  • 1
    are you sure that this flags will not affect altering table with a lot of data adding keys and constraint ? Jul 29 '19 at 6:10
  • I've done some brief testing. With UNIQUE_CHECKS=0 you'll get an error (duplicate entry) if you try to add a unique constraint on a column where the data isn't unique. With FOREIGN_KEY_CHECKS=0 you will not get an error if you add a foreign key constraint on a column that points to non-existent rows in the parent table. So maybe FOREIGN_KEY_CHECKS=0 can actually speed up foreign key/index creation?
    – dbdemon
    Jul 29 '19 at 8:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.