Re: Question: Performance impact of adding Nullable FK to a new column in large tables
Hi Jeff, Thank you for your response! To clarify, my question is more hypothetical and focused on understanding how the database engine handles this specific scenario. If the column being added is new and starts with all values as NULL, does it make sense to temporarily disable FOREIGN_KEY_CHECKS during the ALTER TABLE operation? My main concern is whether the database performs any additional checks or processes, even though the column is empty (NULL), or if the operation is already optimized and skipping the checks automatically. I'm not debating the use of foreign keys or their impact on runtime performance. I'm specifically curious if disabling FOREIGN_KEY_CHECKS in this situation saves any resources during the ALTER TABLE, or if it's unnecessary because the database doesn't do anything in this case that would benefit from skipping checks. Best regards, Guillermo El vie, 20 dic 2024 a la(s) 5:14 p.m., Jeff Dyke (jeff.dyke@gmail.com) escribió:
While i'm not really sure what performance issues you are referring to. FK are going to have a small performance hit on row changes, but for data integrity, its what you need. When you start to design FKs with Null values, you are talking some of the work off of the database work that can be done for you, and moving it to your developers. Also parent_id should be indexed to assist in select performance.
Most times that I have seen this done in the past, its normally removed and the index is kept. if parent_ids are going to be duplicated and only null for a period of time, that is a good one to many relationships, that will enforce integrity once populated.
If you think this will improve overall performance, that is not the goal of Foreign Keys.
On Fri, Dec 20, 2024 at 11:03 AM Guillermo Céspedes Tabárez via discuss <discuss@lists.mariadb.org> wrote:
Hi,
I’d like to understand the performance impact of adding a FOREIGN KEY constraint to a new column in a large table. If the column is nullable and defaults to NULL, does the engine perform any checks or validations on existing rows? Or is the operation optimized since the column starts with all values as NULL?
Here’s a simplified example:
ALTER TABLE child_table ADD COLUMN parent_id INT NULL, ADD CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent_table (id) ON UPDATE CASCADE ON DELETE SET NULL;
Both child_table and parent_table are large, and the new column (parent_id) is empty initially.
Does adding the foreign key have any measurable performance impact in this case? Additionally, would it make sense to temporarily disable FOREIGN_KEY_CHECKS for this ALTER TABLE operation and then re-enable it? Could this save any resources or improve performance?
Thanks for any insights!
Best regards, Guillermo. _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
For that, no. As soon as you have links in parent_id, you'll need to determine how you want to alter that table, but mariadb allows the value to be null. Given I don't know what future alters you'll execute, I would only add FOREIGN_KEY_CHECKS=0. if fails without, and based on your assessment of the data to be altered. NULLS have consequences. Thats my main point. I would not worry about the performance concerns, as you restated them. Its going to be a zero sum game. On Fri, Dec 20, 2024 at 3:43 PM Guillermo Céspedes Tabárez < dev.dertin@gmail.com> wrote:
Hi Jeff, Thank you for your response!
To clarify, my question is more hypothetical and focused on understanding how the database engine handles this specific scenario.
If the column being added is new and starts with all values as NULL, does it make sense to temporarily disable FOREIGN_KEY_CHECKS during the ALTER TABLE operation? My main concern is whether the database performs any additional checks or processes, even though the column is empty (NULL), or if the operation is already optimized and skipping the checks automatically.
I'm not debating the use of foreign keys or their impact on runtime performance. I'm specifically curious if disabling FOREIGN_KEY_CHECKS in this situation saves any resources during the ALTER TABLE, or if it's unnecessary because the database doesn't do anything in this case that would benefit from skipping checks.
Best regards, Guillermo
El vie, 20 dic 2024 a la(s) 5:14 p.m., Jeff Dyke (jeff.dyke@gmail.com) escribió:
While i'm not really sure what performance issues you are referring to.
FK are going to have a small performance hit on row changes, but for data integrity, its what you need. When you start to design FKs with Null values, you are talking some of the work off of the database work that can be done for you, and moving it to your developers. Also parent_id should be indexed to assist in select performance.
Most times that I have seen this done in the past, its normally removed
and the index is kept. if parent_ids are going to be duplicated and only null for a period of time, that is a good one to many relationships, that will enforce integrity once populated.
If you think this will improve overall performance, that is not the goal
of Foreign Keys.
On Fri, Dec 20, 2024 at 11:03 AM Guillermo Céspedes Tabárez via discuss <
discuss@lists.mariadb.org> wrote:
Hi,
I’d like to understand the performance impact of adding a FOREIGN KEY constraint to a new column in a large table. If the column is nullable and defaults to NULL, does the engine perform any checks or validations on existing rows? Or is the operation optimized since the column starts with all values as NULL?
Here’s a simplified example:
ALTER TABLE child_table ADD COLUMN parent_id INT NULL, ADD CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent_table (id) ON UPDATE CASCADE ON DELETE SET NULL;
Both child_table and parent_table are large, and the new column (parent_id) is empty initially.
Does adding the foreign key have any measurable performance impact in this case? Additionally, would it make sense to temporarily disable FOREIGN_KEY_CHECKS for this ALTER TABLE operation and then re-enable it? Could this save any resources or improve performance?
Thanks for any insights!
Best regards, Guillermo. _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
participants (2)
-
Guillermo Céspedes Tabárez -
Jeff Dyke