0

Currently attempting to add foreign keys to my table which contains about 27M rows, but it has been running for three hours. Is this normal?

Here's the query:

ALTER table lineitem
ADD foreign key(L_PARTKEY, L_SUPPKEY) references partsupp(PS_PARTKEY, PS_SUPPKEY),
ADD foreign key(L_PARTKEY) references part(P_PARTKEY),
ADD foreign key(L_SUPPKEY) references  supplier(S_SUPPKEY);
user2738003
  • 13
  • 3
  • altering any table (specially if the operational columns have values) with data takes longer time. you can try creating new table with all required contarints first and then inserting values into the new table. – Shantanu Dec 08 '14 at 09:17
  • In a word yes, probably due to building fk indexes or the method used to do the alteration see - http://stackoverflow.com/questions/12774709/mysql-very-slow-for-alter-table-query – kayakpim Dec 08 '14 at 09:18
  • @Shantanu which is exactly what ALTER TABLE does internally if it can't perform the operation online, so it will take about the same amount of time ... – Hartmut Holzgraefe Dec 08 '14 at 09:31

0 Answers0