Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts

    InnoDB vs MyISAM

    Okay so I'm in a dilemma for choosing which table type to use... Both have features I want...

    On the one hand MyISAM supports FULL-TEXT Searches which are very useful.

    On the other hand InnoDB tables allow for TRANSACTIONAL coding
    (The locking of table rows during inserts, updates, and deletes and being able to rollback changes if not all of the sql queries are executed)

    What would you recommend and why?

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,990
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Dont take this as expert advice !

    I allways chose MyISAM coz I readed it is wery fast and can be repaired.

    As folllowed from that statement inodb is 'slow' and can't be repaired.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    another thing to consider is data integrity, myisam does not support foreign keys.

    note that you can mix and match table types as needed. If you don't need full text indexing on the majority of your tables make them innodb type and split off what you need into myisam to take advantage of full text indexes.

    As for repairs, if you are running a live production server and aren't running daily cron job to back up your data you have more things to worry about than whether or not you can run a repair against a table.

  • #4
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts
    The database will be ran on a SAM system that is raided (so there will be an active backup) and then all databases will be backed up daily as a whole with a backup program.

    I'd really rather not mix the table types for the sake of sanity but I've thought about that too.

  • #5
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,990
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Note about repair:

    I had a crontab job doing backup sqldump each day, by pure luck I looked one day and see the backup file was extremly small, i.e. one table corrupted, and nothing has been backed up, doh file was created.

    Hence I think repair could be useful.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #6
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts
    Thanks for the replies... hopefully in the future they advance the abilities of these tables to incorporate all of the features into one table type.

  • #7
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Quote Originally Posted by Coyote6 View Post
    The database will be ran on a SAM system that is raided (so there will be an active backup) and then all databases will be backed up daily as a whole with a backup program.
    RAID is not a backup. But if your SAN is being backed up onto tape (or similar) everyday, that should be sufficient.
    OracleGuy

  • #8
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts
    I was referring to it as more of a fail safe back up. It is writing an exact copy of the data to a separate hard drive(s)... Yes if something corrupts the data the data will be corrupted on both drives but in a case of a hard drive failure it can be considered a type of back up to the current data... but yes it is also being backed up to another source nightly.

    And oops I meant SAN system not SAM.

  • #9
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by BubikolRamios View Post
    Note about repair:

    I had a crontab job doing backup sqldump each day, by pure luck I looked one day and see the backup file was extremly small, i.e. one table corrupted, and nothing has been backed up, doh file was created.

    Hence I think repair could be useful.
    Yes a repair could be useful, but you are more likely to need TRANSACTIONS and FOREIGN KEYs enforced, neither of which are supported by MyISAM tables.

    So what you are going to gain by being able to do a repair, which may crop up once in a very little while, or lose two major components, which on a real system you are to use daily if not more often.

    Which do you think is more vital?

  • #10
    New to the CF scene
    Join Date
    Jul 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MYISAM OR innoDB

    MyISAM OR InnoDB
    Required full text Search myISAM
    Require Transactions innoDB
    frequent select queries myISAM
    frequent insert,update,delete innoDB
    Row Locking (multi processing on single table) innoDB
    Relational base design innoDB

    Hi, I have briefly discuss this matter by table so you can conclude which has to be chosen either innodb or MyISAM.
    http://developer99.blogspot.com/2011...vs-myisam.html


  •  

    Tags for this Thread

    Posting Permissions

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