After converting MySQL key columns to a FOREIGN Columns the site slowed down -

i never used foreign keys before. placed id key column not necessary foreign key. acts foreign key not.

so if have following 2 tables

create table `accounts` (  `account_id` int(11) unsigned not null auto_increment,  `name` varchar(60) not null,  `owner_id` int(11) unsigned not null,  primary key (`account_id`),  key `owner_id` (`owner_id`) ) engine=innodb default charset=utf8   create table `users` (  `user_id` int(11) unsigned not null auto_increment,  `name` varchar(60) not null  primary key (`user_id`) ) engine=innodb default charset=utf8 

the column account.owner_id linked users.user_id without setting foreign key relationship.

so can this

select a.name account_name, u.name user_name accounts inner join users u on u.user_id = a.owner_id 

so after learning foreign keys , have created foreign key so.

alter table accounts  add constraint fk_owner_id foreign key(owner_id) references users(user_id) on delete no action on update cascade; 

however, notices performance change system slowed down lot. not sure if adding foreign keys reuse performance or no? or there performance reduction when adding foreign keys table? please note that there lots of tables , columns in database. many inner/left/right joins , columns identified them foreign keys indexed. notices did not add indexes columns of exists prior adding foreign keys database.

my question, foreign keys reduce performance on update/insert/delete/select? there benefit of adding foreign key constraint when specified on delete no action on update no action?

thanks

the foreign key constraint means insert fk column has check if value exists in referenced column of users. there overhead this, it's index lookup definition (probably pk lookup) cost shouldn't high.

foreign keys create shared lock on parent table during updates on child table. can in way of concurrent updates against table, , make seem system has slower performance. see http://www.mysqlperformanceblog.com/2006/12/12/innodb-locking-and-foreign-keys/

the foreign key implicitly created index on fk column, if no index exists. every insert, update, delete has modify indexes of table @ time of change, there bit of overhead. reason, people indexes "hurt" performance of insert, update, delete. it's not simple -- index supports conditions in clause can make update or delete run faster finding affected rows more efficiently.

---

re comment:

yes, update child tables creates exclusive lock on modified row in child table. expected this. action creates shared lock on referenced row in clients. number of sessions may independently create shared locks on same row (hence name shared). exclusive lock requires there no lock of type. if there shared locks outstanding on row in clients, direct updates row in clients can't required exclusive lock.

the purpose of these shared locks row in clients doesn't removed or modified while updating row depends on it. in other words, "don't delete parent." depending on frequency of updates , duration of transactions, make hard perform updates parent row.

one way mitigate try make locks live shorter periods of time, finishing work transactions promptly, , commit.