Solved

Performance Implications of Referential Integrity (RI)?

Posted on 2010-09-07
5
747 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Date 7 28
Need more granular date groupings 4 45
Database movement 2 16
Conditional Array Element 7 28
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…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
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 create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

710 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