Solved

Performance Implications of Referential Integrity (RI)?

Posted on 2010-09-07
5
739 Views
Last Modified: 2012-05-10
I am designing a MySQL database using the "MySQL Workbench". I have created a design which includes lots of RI, but what are the performance implications of RI?

Please could you refer to the following illustration of the WordPress database:
http://codex.wordpress.org/images/9/9e/WP3.0-ERD.png

Table: wp_term_taxonomy
Attribute: "parent" may optionally refer to another "wp_term_taxonomy"

- What reasons might the WordPress designers have chosen to not enforce RI for term hierarchies?

- Are there any performance benefits with RI and foreign keys?
0
Comment
Question by:numberkruncher
  • 2
  • 2
5 Comments
 
LVL 4

Accepted Solution

by:
Jezbit earned 167 total points
Comment Utility
Referential integrity is a property of business data. It manifests itself as business rules defining relationships between entities in a logical model. When a data architect transforms this logical model into a physical database model, they must decide how the RI will be enforced. The most common choices are application-enforced and DBMS-enforced.

DBMS-enforced referential integrity is a best practice. In cases where implementing RI this way may cause problems, DBAs can use the NOT ENFORCED option to ease restrictions.

Due to the performance impact of enabling Referential Integrity (RI) for a dimensional model, foreign key constraints are usually not enforced in the database.

0
 
LVL 34

Assisted Solution

by:Beverley Portlock
Beverley Portlock earned 333 total points
Comment Utility
The biggest "gotcha" with MySQL is that the tables must run the InnoDB storage engine rather than the default (and faster) MyISAM engine.

Any additional checks you run will, of course, slow the DB. The are no performance benefits with RI, the benefits are increased integrity and, as with all tradeoffs, if you gain something (integrity) then you lose something (speed).

5 to 10 years ago this may have been a problem, but nowadays given the speed of servers I would not expect the perfomance difference to be noticeable unless you go crazy and define bucket loads of relations and put the resulting DB on a server with poor I/O performance.

The other matter to consider is the amount of CONCURRENT traffic. Unless you have a really busy website then you will probably only have one user accessing the database at any given instant. The only other consideration is if your webserver is on a shared host because this will introduce a bottleneck in terms of I/O.

So, in summary, unless you are expecting a huge amount of traffic or are running on a slow server then I do not think you will notice the performance degradation.
0
 
LVL 13

Author Comment

by:numberkruncher
Comment Utility
Thank you guys, that is interesting.

With regards to traffic, the website will have on average between 200-500 users at any given time. So I am thinking that I should switch off "Foreign Keys" when exporting the create script from "MySQL Workbench".

Should I still be using heavy RI in "MySQL Workbench"? Is it possible to export selective RI?
0
 
LVL 34

Assisted Solution

by:Beverley Portlock
Beverley Portlock earned 333 total points
Comment Utility
As long as updates are made to the database via scripts which honour the RI rules then there is nothing to be gained by putting the RI rules into the database. If there is a possibility that scripts that may not honour your rules (plug-in code?) then putting RI in will protect the database and break the offending code.

Some RI is more important than others. By careful coding and judicious use of LEFT JOINs and INNER JOINS I've rarely needed RI in the database.

Try it anyway. It is far easier to turn RI off than it is to turn it on. If it works OK then leave it on. If performance sucks, turn it off. If, after a few days, you can turn itt back on again then that indicates that the scripts are maintaining the RI and so (perversely enough) you can leave it off. If the RI does not want to switch back on it is because the data is breaking the constraints which means that you have a badly behaved script.
0
 
LVL 13

Author Closing Comment

by:numberkruncher
Comment Utility
Thanks for the help guys, and sorry for the late response.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now