SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict nabeel's Avatar
    Join Date
    Nov 2002
    Location
    in westchester county, ny
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    prevalence of using innodb

    Hey all,

    I'm developing some software, and I'm thinking of using InnoDB, for the use of foreign keys.
    Basically I'm FKing 3 character codes, that could be changed (not often), and maybe other peices that won't be updated too often.

    So, in the end, my question is if it's worth it to use InnoDB and go with FK, or just write the code to change those values myself.

    The advantage to MyISAM would be that it's "backup" able by my host in particular, and since it's the default, don't have to worry about compat/installation issues.

    There will be frequent INSERT and UPDATE, especially when data is tossed to it.

    Any suggestions? I'm sure many of you have more experience with this than me.

    -Nabeel

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    InnoDB is definitely widely used, and the features it supports are implemented in all the top RDBMS's, but missing in MyISAM. Unless you have a pretty large data set and query volume, the performance differences between the two are pretty small, but you can feel much safer about your data integrity if it's enforced by constraints on an InnoDB table rather than code you have to write.

    If you have performance concerns, then figure out which storage engine is actually better for your application rather than which lets you use some host's backup button. Backing up a database is simple.

  3. #3
    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 nabeel View Post
    There will be frequent INSERT and UPDATE, especially when data is tossed to it.
    so, when data is not "tossed to it" there will still also be frequent INSERT and UPDATE, but perhaps not as much?

    MyISAM locks the table when doing inserts, updates, and deletes

    InnoDB locks the row

    might may a difference, depending on the amount of "tossing," eh

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict nabeel's Avatar
    Join Date
    Nov 2002
    Location
    in westchester county, ny
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    so, when data is not "tossed to it" there will still also be frequent INSERT and UPDATE, but perhaps not as much?

    MyISAM locks the table when doing inserts, updates, and deletes

    InnoDB locks the row

    might may a difference, depending on the amount of "tossing," eh

    Ahah, never quote thought of it that way

    Non-table locking is good. There's automatic in-flight reporting, a table lock in that case would be bad, since it's updating positions to the main system. That was one of the reasons that I preferred InnoDB. There's also alot of "referential" data in the tables, which point back to another. I like the "disconnected but connected" approach of the foreign keys, alleviates alot of pain on my end.

    My only concern was maybe that only ~50% of hosts actually enable it (I think that's the stat someone threw at me). But I guess that's wrong. But InnoDB for tossers it is!
    Thanks guys

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by nabeel View Post
    My only concern was maybe that only ~50% of hosts actually enable it (I think that's the stat someone threw at me). But I guess that's wrong.
    Nobody's said anything contradictory to that. Many hosts disable it so they don't have to tune InnoDB performance on their shared instances.

  6. #6
    SitePoint Addict nabeel's Avatar
    Join Date
    Nov 2002
    Location
    in westchester county, ny
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    Nobody's said anything contradictory to that. Many hosts disable it so they don't have to tune InnoDB performance on their shared instances.
    Ah gotcha.Well I guess I'll have to then offer some type of "backup" in that case.
    Thanks !


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
  •