Mysql – Is inserting into a table with a foreign key constraint slower than inserting without constraint

auroraforeign keyinsertMySQL

When using MySQL (actually, Aurora), will inserting into a table with a foreign key constraint be slower than the same table without the constraint?

Best Answer

INSERT (etc) must check that there are no FOREIGN KEY violations. Hence it is slower. Think of it as checking to see if an entry is in an index.

The actual cost depends heavily on whether the block of the other index is currently cached, which depends on size of entire dataset, workingset size, speed of disk, etc. I have not encountered a case where FKs made the difference between the system being 'viable' versus 'too slow'.

Related Solutions

Postgresql – Postgres – Create table with foreign key constraint too slow

Your query got locked waiting for something - my bet is that it was waiting for some other transaction to finish.

When you're doing the create table, just issue (in another psql session):

select * from pg_locks where pid = XXX and not granted;

where xxx is pid of backend doing the create table.

This will show you what is the lock that the create table is waiting for.

MySQL – Foreign Key Constraint Fails

You need to have already declared the table that the foreign key references, before you can define a foreign key that references it.

Once you declare the second table, you can then declare the first table. Tested here on MySQL 5.5.27.

If you need to bypass the validation, you can do this:

SET FOREIGN_KEY_CHECKS = 0;
-- declare tables
SET FOREIGN_KEY_CHECKS = 1;

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_foreign_key_checks

Best Answer

Related Solutions

Postgresql – Postgres – Create table with foreign key constraint too slow

MySQL – Foreign Key Constraint Fails

Related Question