Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Performance Implications of Referential Integrity (RI)?

Posted on 2010-09-07
5
Medium Priority
?
756 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 668 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 1332 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 1332 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

782 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