Menu
Dropping a foreign key on a table with 215k+ (with alter table) records seems to take a long time (17+ minutes). Is it possible to somehow speed up the process?
SQL: ALTER TABLE sales_flat_order_grid DROP FOREIGN KEY FK_SALES_FLAT_ORDER_GRID_STORE;
It is a magento upgrade that takes ages
537
Big Data, Data Breaches, Foreign Keys and The Future One more vital point of concern is that foreign keys would surely slow down both INSERT s and LOAD DATA INFILE operations since the index would have to be updated together with the data. The more data you have, the more apparent the problem will become.
It's a common mistake to avoid creating foreign keys in a database because they negatively impact the performance. It is true that foreign keys will impact INSERT, UPDATE and DELETE statements because they are data checking, but they improve the overall performance of a database.
Foreign key indexes can significantly improve performance for queries that involve joins between the parent and child tables.
A FOREIGN KEY constraint has no direct impact on read performance. The referencing column does not even have to be indexed (as opposed to the referenced column).
Unless you are using InnoDB Plugin (and by default, in MySQL 5.0 and 5.1 you are not), removing an index require rebuilding the whole table.
If you can't upgrade MySQL, you should either look at online-schema-change (involving transfering all of the data to a new table without the index) or stop the site, minimize any I/O activity and wait the operation to complete.
100
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
