Solved

Performance Implications of Referential Integrity (RI)?

Posted on 2010-09-07
5
742 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
ID: 33617259
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
ID: 33617284
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
ID: 33618299
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
ID: 33618409
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
ID: 33648839
Thanks for the help guys, and sorry for the late response.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

932 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

10 Experts available now in Live!

Get 1:1 Help Now