SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Zealot Zurev's Avatar
    Join Date
    Feb 2009
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What I don't understand - DB Efficiency/Optimization

    Alright well, I'm a college student majoring in CS, and database administration has always triggered an interest, I love how in depth it can get just storing data and setting up the best possible ways to access it, archiving databases, securing them from your own end, etc.

    Well I've been working on a fairly large project in PHP/MySQL - Using codeigniter, and well I've been dealing with databases for a while and I understand the concept of foreign keys, except I've yet to be able to implement them, for example, if I have a table users, and a table posts, I'll simply put author_id field in the posts table. So when I need to select a users posts, I can do a join query, or whatever. Now after discussing the concept of it with one of my professors, she said as a general rule of thumb, before 10,000 records, I really shouldn't have to worry about optimizing a database to the fullest, however I should still plan out tables well.

    So I have a series of tables in my latest projects, and using the profiling tools in codeigniter, the most complicated of queries is showing in the .0010's of seconds (ten thousandths I believe ). So should I really be worried about optimizing my database, and if so, how would the concept of foreign keys help speed/efficiency? She also told me to seriously look into indexing, however I read some things that said it causes general overhead in the database server.

    I see the forum has a lot of great minds, so any input would definitely be beneficial, any at all. Thank you.

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,053
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    First you need to determine whether the majority of tables created support foreign keys. Some engines such as; MYISAM don't support actual foreign key constraints. You also need to balance foreign key support with the ability to do fill text searching. In most cases innodb is used, which does support foreign key constraints, but you loss full text searching capabilities. Otherwise, indexes can be placed on the foreign keys, known to be used in a majority of joins, to optimize query efficiency. In my opinion its always good define indexes in the beginning. That is less work that needs to o done at later date, perhaps by someone not as familiar with software as the creator. When I work with innodb I always define foreign keys.

  3. #3
    SitePoint Zealot Zurev's Avatar
    Join Date
    Feb 2009
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I set them all up initially as InnoDB for the possibility of expanding into foreign key setup.

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,053
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Than foreign keys should have been defined from the beginning. If not for optimization definitely for data integrity.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    foreign keys do nothing for performance

    if there is any performance improvement, it is not the foreign keys themselves, but the result of the indexes being created to support the foreign keys (the indexes are created automatically after version 4.1.2, before that you had to create them yourself)

    that said, foreign keys do enforce data integrity, which, if you think about it, is pretty much the most important feature you'd want from a database
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot Zurev's Avatar
    Join Date
    Feb 2009
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for the responses, so setting indexes is pointless unless you are setting up foreign keys since it's automatic? Also, by data integrity you mean in the way that I could make it so if an authorID is deleted, it deletes all posts associated with that authorID?

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,053
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Its *possible* to have a post assigned to an author that doesn't exist without a true foreign key constraint.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Zurev View Post
    so setting indexes is pointless unless you are setting up foreign keys since it's automatic?
    no no no

    setting up indexes is vitally important for performance

    if you declare foreign keys you get indexes as a byproduct

    but if you don't declare foreign keys, you still need indexes for performance

    Quote Originally Posted by Zurev View Post
    Also, by data integrity you mean in the way that I could make it so if an authorID is deleted, it deletes all posts associated with that authorID?
    that's just one aspect of it

    more important is that bad data never gets into the database
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot Zurev's Avatar
    Join Date
    Feb 2009
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, well since I don't have foreign keys set up and I need to setup indexes, what would be the most optimal field to set them up on? I'm assuming postID since every single post is retrieved by it's ID in the row.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Zurev View Post
    Okwhat would be the most optimal field to set them up on?
    any column referenced in a WHERE condition that isn't already indexed as a primary key
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Zurev View Post
    Also, by data integrity you mean in the way that I could make it so if an authorID is deleted, it deletes all posts associated with that authorID?
    data integrity also means that if you're using a database type that doesn't support FK's then you would have to check programatically if the FK exists before inserting a row into a table

    example

    tblAuthors

    fldAuthorId
    fldFname
    fldLname

    tblTitles

    fldTitleId
    fldTitle
    fldAuthorId

    before inserting a row of data in tblTitles you should run a query to ensure the value of fldAuthorId you are about to enter in tblTitles already exists in tblAuthors.

    the same applies when deleting records in db's that do not support FK's. when deleting records from a table, you will have to programatically delete all associated records in other tables.

  12. #12
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,910
    Mentioned
    96 Post(s)
    Tagged
    0 Thread(s)
    A couple of things to consider with regard to MyISAM vs Innodb


    • Locking Levels
    • Transaction Support (InnoDB supports transactions, AFAIK MyISAM doesn't)
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  13. #13
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    A couple of things to consider with regard to MyISAM vs Innodb


    • Locking Levels
    • Transaction Support (InnoDB supports transactions, AFAIK MyISAM doesn't)
    yep it doesn't

    but historically, I'm not sure if the situation still applies, many web hosting providers only supported MyISAM because it performed better due to the lack of overheads associated with supporting FK's, locking, transactions etc.

  14. #14
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,011
    Mentioned
    57 Post(s)
    Tagged
    0 Thread(s)
    Something to consider with foreign keys - ideally they should be unique across the database. For example, if you have a post table, any foreign key pointing back to that table should have the same name - probably postid. No other table should be pointed to using a foreign key named postid.

    Do whatever maintenance programmers who follow you a huge favor and follow this rule. If you need to join two different tables that both have a foreign key to a third table you can always use aliases (left join mytable AS myalias) to disambiguate the keys for that query.

  15. #15
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,011
    Mentioned
    57 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    A couple of things to consider with regard to MyISAM vs Innodb


    • Locking Levels
    • Transaction Support (InnoDB supports transactions, AFAIK MyISAM doesn't)
    A major problem with Innodb is that that MySQLHotCopy doesn't work with it (this is a perl script that locks the tables, copies the table files, and then unlocks the tables). This means that the only way to copy innodb tables that I know of is mysqldump. For a database in the gigabytes this can take hours to do, where a hotcopy can be done in a few minutes.

    Disclaimer - this is based on experience some years ago with MySQL 4.0. This problem may have been addressed by now.

  16. #16
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,053
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Michael Morris
    Something to consider with foreign keys - ideally they should be unique across the database. For example, if you have a post table, any foreign key pointing back to that table should have the same name - probably postid. No other table should be pointed to using a foreign key named postid.

    Do whatever maintenance programmers who follow you a huge favor and follow this rule. If you need to join two different tables that both have a foreign key to a third table you can always use aliases (left join mytable AS myalias) to disambiguate the keys for that query.
    Not a hard and fast a rule and it can really be a judgment call in many cases. For example, naming a column authors_id rather than users_id provides a contextual purpose whereas the later does not. Another example is owners_id over the generic users_id. Using something generic may seem like a good idea, but in my opinion the meaning of columns are easier to grasp when column names are derived from an actual context based on the business goals at hand rather than a generic reference.

  17. #17
    SitePoint Member
    Join Date
    Dec 2010
    Location
    Silicon Valley
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For backs ups I've never found mysqldump to be a problem. I use it all the time to create database snapshots.

    I start with a snapshot, use replication to keep all the slaves up to date and pretty much have zero down time.

  18. #18
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Michael Morris View Post
    A major problem with Innodb is that that MySQLHotCopy doesn't work with it (this is a perl script that locks the tables, copies the table files, and then unlocks the tables). This means that the only way to copy innodb tables that I know of is mysqldump. For a database in the gigabytes this can take hours to do, where a hotcopy can be done in a few minutes.
    If your file system has snapshot support, then that's one way to deal with the problem.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •