« Version Control your work..  |  Main  |  Hibernate weirdness with property names »

Create a Index for every Foreign Key constraint created

Posted by: Pramod Sadalage on 07/15/2008

When creating a Foreign Key constraint on the database as shown below

ALTER TABLE BOOK ADD 
    (CONSTRAINT FK_BOOK_AUTHOR FOREIGN KEY (AUTHORID) 
     REFERENCES AUTHOR)
/

In the above example we are telling the database to check if the BOOK.AUTHORID is a valid value in the Author.AuthorID. When the Author table is being changed, the database does data verification on the BOOK table using SELECT against the BOOK table for the AUTHORID some thing like this

SELECT count(*) FROM BOOK WHERE AUTHORID = nnnn

Basically the database server is trying to check if it has children rows for the row that just changed (inserted or deleted). While doing this if there is not index on BOOK.AUTHORID, the database will have to scan the whole table which is slow. Hence when creating a Foreign Key constraint, remember to create a corresponding INDEX on the table, so that the performance does not degrade, or when observing slow performance on a database after you put in Foreign Key constraints. Make sure to look for Indexes on the columns that are constrained.


Add To DZone

Pramod Sadalage's complete blog can be found at: http://www.sadalage.com/

About Pramod Sadalage

Pramod Sadalage

Pramod Sadalage is the co-author of the 2007 Jolt Productivity Award winning

"Refactoring Databases: Evolutionary Database Development", a Martin Fowler

signature series book and author of "Recipes for Continuous Database

Integration". Pramod works as a DBA and developer at ThoughtWorks. He works on

large custom-developed applications that use agile methodologies. While on these

projects, he pioneered the practices and processes of agility in the database.

Pramod writes and speaks about these concepts and practices to help those

interested in using agile practices on databases. He has written and presented

about database administration on XP projects, the adoption of agile processes

with databases, and the impact of agile practices on database administration and

design. Pramod is the owner and founder of the agiledatabases Yahoo! group; he

moderates group discussions and helps group members learn about agile databases.

When he is not working, you can find him spending time with his wife Rupali and

daughter Arula, and trying to improve his running.

More About Pramod »

Why Attend the NFJS Tour?

  • » Cutting-Edge Technologies
  • » Agile Practices
  • » Peer Exchange

Current Topics:

  • Languages on the JVM: Scala, Groovy, Clojure
  • Enterprise Java
  • Core Java, Java 7
  • Agility
  • Testing: Geb, Spock, Easyb
  • REST
  • NoSQL: MongoDB, Cassandra
  • Hadoop
  • Spring 3
  • Automation Tools: Git, Hudson, Sonar
  • HTML5, Ajax, jQuery, Usability
  • Mobile Applications - iPhone and Android
  • More...
Learn More »

NFJS, the Magazine

NFJS, the Magazine
May Issue Now Available

  • Client-Side MVC with Spine.js, Part 1

    by Craig Walls

  • On Prototypal Inheritance, Part 2

    by Raju Gandhi

  • Making use of Scala Lazy Collections

    by Venkat Subramaniam

  • Integration Testing Web Applications Using Gradle

    by Kenneth Kousen
Learn More »