0

I have a website that needs to do a lot of active searching of users. I have a User table which contains links to all the full user details but that is only really of interest when looking at your own account. When searching for other users, there is very limited information you need so in order to make searches faster and more efficient, every time you update your user details, the code writes an entry to a separate table called UserLight - which only contains about 8 columns and is all pure data - ie no links to other child tables or collection objects, just string data for speed. Each user can only have one UserLight entry at a time which is the summary representation of how their account appears to other users.

My question is for performance, does it matter that I am making the UserId a foreign key constraint with the User table? So you cannot create a UserLight entry without the corresponding row in User, and also so when you delete the User row, it automatically cascades and deletes the UserLight entry. That is ideal and how I would like to have it but I'm just wondering if having this FK constraint on the UserLight table in any way slows down the performance on read or write operations to/from this table? If it does, I am happy to drop the FK constraint and have a completely isolated table with no constraints or external references to other objects to speed up performance, and just manage housekeeping manually, but if the FK constraint doesnt affect performance at all - I would prefer to keep it.

NZJames
  • 4,963
  • 15
  • 50
  • 100
  • 2
    The foreign key constraint has little to do with performance. Having the right joins and the right indexes will be much more detrimental to performance. As for how it would effect your database? Who knows? There are too many factors and you need to test with and without to find out. – Oded Nov 29 '12 at 11:18
  • Actually they have impact in case of cascading deletes depending on your dbms. Sql server examlle http://stackoverflow.com/q/2525191/532498 – Pleun Nov 29 '12 at 11:20
  • Foreign key can also *improve* performance because it gives the optimizer more information about how the data is related. See here for example: http://stackoverflow.com/a/8154375/330315 or here http://aberdave.blogspot.de/2011/04/referential-integrity-and-optimizer.html –  Nov 29 '12 at 11:27
  • Not implying that you need to delete the foreign key, just be careful with cascading deletes. Stick eith retrict in these cases – Pleun Nov 30 '12 at 06:25

1 Answers1

1

It will not hamper your performance instead its preferred to have data constrained so as to avoid insert/delete/update anomalies.

Ajith Sasidharan
  • 1,155
  • 7
  • 7