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.
Re:heh
Abigail on 2006-12-12T13:30:10
No, not even a counter script. Remember, MyISAM doesn't give you transactions, and for a counter script, you generally want to do:and you want to do that atomically.update counter set value = value + 1
select value from counterWithout transactions, why even bother having a database server?
Re:heh (Counter script)
Corion on 2006-12-12T15:03:36
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 ... Re:heh (Counter script)
Abigail on 2006-12-12T16:33:11
Yes, but you still want to wrap those statements into a single transaction.Re:heh
Dom2 on 2006-12-12T13:34:52
As far as I know (I don't use MySQL) the only reason is full text search.-Dom
Re:heh
Ovid on 2006-12-12T14:19:51
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).
Re:heh
mpeters on 2006-12-12T17:29:11
If it was primarily a read-only server with heavy usage and only being written to in an extremely controlled mannerWe 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.
Re:heh
chromatic on 2006-12-12T18:57:09
A lot of people have never realized that this was MySQL's target task for many, many years.
Re:heh
Ovid on 2006-12-12T19:23:24
I initially laughed my head off at that because I thought you were talking about using MySQL to support broken Web counters
:) Re:heh
chromatic on 2006-12-12T20:33:28
That's PHP and MySQL, actually, but I don't blame either.
Re:heh
DAxelrod on 2006-12-14T20:26:45
Nor should we blame them, lest Perl and Sendmail are blamed for buggy form-to-mail scripts.:)
Re:Be Warned
jk2addict on 2006-12-12T13:57:04
Oops. Forgot the other link:
http://jpipes.com/presentations/maximum_velocity_mysql.pdfRe:Be Warned
Abigail on 2006-12-12T14:29:50
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.
Re:Be Warned
Ovid on 2006-12-12T15:08:54
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.
Re:Be Warned
jk2addict on 2006-12-12T16:16:00
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.:-)
Re:Hot backups
merlyn on 2006-12-14T00:47:34
Even more reason to ignore MySQL and move to PostgreSQL, where you can make hot backups for free.Re:Hot backups
acme on 2006-12-14T08:05:07
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;-) Re:Hot backups
Abigail on 2006-12-14T08:20:56
Now, if only MySQL had a more useful replication than replication of statements.Re:Hot backups
acme on 2006-12-14T14:03:27
5.1 has row-based replication at least.Re:Hot backups
merlyn on 2006-12-16T06:41:14
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.
