Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

Ovid
  (email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Tuesday December 12, 2006
04:48 AM

MyISAM versus InnoDB

[ #31890 ]

As seen here:

A general guideline could be as follows: if you require multi-statement transactions, advanced isolation levels and row-level locking, foreign key constraints, or otherwise have a requirement for ACID features, go for InnoDB. Otherwise, simply use MyISAM, the default.

I think I can restate that just a little clearer: don't use MyISAM.

Less dogmatically, unless you know exactly why you need MyISAM and you have the data to prove that this is what you need, don't use it. Period. And I want to see the data first. And how you collected it. And whether it was in a real-world environment. And I want to know what problem you're trying to solve and why the broken technology of MyISAM is the best choice. There are legitimate uses for MyISAM, but not many.


List all Journal entries
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

MyISAM versus InnoDB 23 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • > There are legitimate uses for MyISAM, but not many.

    Like maybe a counter script, or some sort of simple log table, which about the most sophisticated things I've ever used it for.
    • No, not even a counter script. Remember, MyISAM doesn't give you transactions, and for a counter script, you generally want to do:

          update counter set value = value + 1
          select value from counter
      and you want to do that atomically.

      Without transactions, why even bother having a database server?

      • Of course, for a counter you want to do

        insert ip into counter
        :-), and for querying it then

        select count(*) from counter
        ... Now, if that's actually better than just an atomic append of a(ny) line to a text file remains to be discussed ...
    • As far as I know (I don't use MySQL) the only reason is full text search.

      -Dom

      • As far as I know (I don't use MySQL) the only reason is full text search.

        If it was primarily a read-only server with heavy usage and only being written to in an extremely controlled manner, MyISAM might be a reasonable choice if you can't get performance any other way (short of paying for Oracle).

        • If it was primarily a read-only server with heavy usage and only being written to in an extremely controlled manner

          We use MyISAM for a dimensional data warehouse. The data in the warehouse is extracted from other databases (which are InnoDB) and are used by clients and software mostly to make complicated reports that just wouldn't be feasible on the normalized InnoDB tables. Since we already know the data is good we just want fast access to the extracted parts.

          • A lot of people have never realized that this was MySQL's target task for many, many years.

            • I initially laughed my head off at that because I thought you were talking about using MySQL to support broken Web counters :)

  • Just choosing one over the other because of transactions is a dangerous game. They don't store the same, and they don't perform the same; even on the same non-transactional queries in some circumstances (count(*), multiple PKs, etc). For that matter, don't just assume your schema should be the same if you plan on using one vs. the other.

    This has always been a decent document to remind me that 'inno has transactions' isn't the only issue to think about:

    http://jpipes.com/presentations/mysql_perf_tuning.pdf [jpipes.com]

    • Oh, agree that 'inno has transactions' isn't the only thing to think about. The fact that Inno has transaction in itself isn't a reason to choice Inno.

      However, the reverse is true. The fact that MyISAM doesn't have transactions is almost always reason enough to not use MyISAM. The fact that it's hard to come up with a case where you can get away with MyISAM is (for me) reason enought to not even consider.

      • That's a good point. No one would choose a car merely because it had a steering wheel and column, but you wouldn't even think about buying one without them.

      • I don't disagree with anything said so far. It's just that whenever people post about Inno vs. MyISAM, I always like to post those links about the hardcore differences....hopefully so future readers will learn from my mistakes and pains of the past. :-)
  • Well the one thing that sticks out is that you can't make hot backups of InnoDB without shelling out $500 per server per year. Pesky commercial software!
    • Even more reason to ignore MySQL and move to PostgreSQL, where you can make hot backups for free.
      --
      • Randal L. Schwartz
      • Stonehenge
      • Hey, if you're going to be aggressive: at least MySQL has realised that people actually scale databases and that replication might be a good idea and so built it into the server ;-)
        • Now, if only MySQL had a more useful replication than replication of statements.
        • And the PostgreSQL team realized that by NOT building it into the distro, they would encourage competing implementations, and in fact, that's what's happened: both free and commercial implementations for free, all of which (I understand) are BETTER than what MySQL has built in.
          --
          • Randal L. Schwartz
          • Stonehenge
Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.