Solved

Performance Implications of Referential Integrity (RI)?

Posted on 2010-09-07
5
743 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

785 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