Which one is faster MyIsam or InnoDB ?

Discussion in 'Databases' started by deriklogov, Jul 11, 2009.

  1. #1
    Which one is faster MyIsam or InnoDB ?
     
    deriklogov, Jul 11, 2009 IP
  2. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,334
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #2
    Depends on the size of your database, the structure of your database, the queries you are doing against it, etc.

    Generally MyISAM is faster, but InnoDB supports record level locking (MyISAM only does table-level locking). So if you are going a lot of queries that are not able to hit indexes and such, using MyISAM can slow things down since additional queries have to wait for the previous one to finish.

    Generally, a well designed database with well designed queries, MyISAM will be faster.
     
    digitalpoint, Jul 11, 2009 IP
  3. chisara

    chisara Peon

    Messages:
    141
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #3
    It depends on your situation data/queryoes/configuration/schema/hardware, run benchmarks with representative data.
    Then draw your own conclusions.
     
    chisara, Jul 13, 2009 IP
  4. HivelocityDD

    HivelocityDD Peon

    Messages:
    179
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Usually MYISAM is enough .. very rarely we need to switch to InnoDB. As shown said when we need record level locking. If you need to make it faster use indexing.
     
    HivelocityDD, Jul 13, 2009 IP
  5. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #5
    Check this out: http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1

    I prefer InnoDB to MyISAM for many reasons, one being that I can usually get better performance from it. I also use foreign keys regularly which are not available without some trigger hacks in MyISAM. Trying to formulating a workable backup plan on MyISAM is almost impossible once your tables get large (Full database locks are not a customer-friendly idea on a production DB). Lastly, MyISAM databases will almost always get corrupted at some point.
     
    jestep, Jul 13, 2009 IP