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(a)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(a)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(a)lists.mariadb.org
>> To unsubscribe send an email to discuss-leave(a)lists.mariadb.org
