[Psih]

Da:Sourcerer, on 16 November 2011 - 10:15 AM, said:

Erm, I beg to differ. If that is really your use case, you might want to have a look at the Archive storage engine. MyISAM tables can be compressed, yes. But if you do so, you can no longer write to them.

I know, but right now I'm fully InnoDB and don't have suitable work for Archive or MyISAM. I just had quite a time with MyISAM on one heavy loaded site with tons of reads and writes with a few extremely hot tables (50/50 reads/writes) peaking at 1500 SQL queries/sec. MyISAM broke every few days and I had to do optimize with repair 2 times a day to keep it working - still got some instances of corruption to handle at peak time. So I invested time into converting everything into InnoDB and some query rewriting for it's specifics - never had a DB issue related to data corruption since for 4 years now.

If someone doesn't want to stick with the InnoDB - he should look at the MariaDB - the successor of MyISAM. Personally never had a chance to work with it, but it's redesigned to be fault-resistant and doesn't corrupt data randomly like MyISAM can - at least people never complained about it.

[Da:Sourcerer]

Huh? I think you're confusing a few things there: First off, I didn't make a stand for MyISAM. I just pointed out that tables managed by the Archive storage engine are more suitable for logs.

Then, MaraiaDB is a community fork of the MySQL RDBMS which has been brought into life after the acquisition of Sun Microsystems (who also owned the rights on MySQL) by Oracle and includes (among other things) some modifications by Percona. InnoDB, MyISAM and Archive are storage engines supported by both.

The "successor" of MyISAM in MariaDB is AriaDB. Likewise has InnoDB been replaced by XtraDB. For compatibility reasons, you can still invoke them by their old names.

If you want my personal impression on MaraiaDB: It's great! MaraiaDB brings some fantastic extensions such as the subquery optimizer. By rigerous fine-tuning, I managed to cut memory consumption on one of my deployment systems by a third. However, there are enough changes to the codebase now that there are queries that only work on one of them. E.g. MariaDB refuses to store broken dates in DATETIME fields. While this is actually a good thing, some applications rely on that (I think WordPress does), making MariaDB unsuitable for general purpose hosting. However, as a rule of thumb, every query that works on MySQL should work on MariaDB as well. So: Develop on MySQL, test and deploy on MariaDB.

[fantgeass]

InnoDB, because MyISAM is obsolete.

[Mike Chojnacki]

It really does depend, I prefer innodb when I know there will be some complicated relationships and the use of foreign keys is required, and I can't really think of a case where the old myisam would be preffered.

[jacmoe]

I usually try to use Innodb, but I regret doing it.. because sometimes you end up on a host with only MyISAM, and everything goes to Hell.

[ekerazha]

InnoDB, what else

[bglee]

Another nod to InnoDB. I admit to being biased. I have a long and sordid history as an Oracle (and other) DBA. Anything without foreign keys is to be sneered at.

[jacmoe]

Yes, but how do you deal with the situation where it's not available?

I tend to use it, but then it bites me when it's not possible to use it.
I still need to look into this, so that my app will function properly under MyISAM.
That would probably require additional checks and injected code?

[Ben]

Never tested it, but for SQLite, there were programs that created triggers to simulate foreign key capabilities (for example this one). Maybe the same aproach could be used with MyISAM tables if your application depends on referential integrity.

[antonywu]

Does Yii framework natively support MariaDB?

[Ivica]

I also think that InnoDB is better, but it might depend on specific project/table. For example InnoDB table doesn't support fulltext search, so in that case you either need to switch to MyIsam table, or stick with InnoDB table, and find some otother solution.

[Da:Sourcerer]

@antonywu
MariaDB is a drop-in replacement. there is no need for a native support. Unless you're doing something really fancy with your queries, it will just work

@Ivica
Actually, InnoDB has got fts support too since MySQL v5.6.x. It's still better to count on a solution like Sphinx or Apache Lucene.

[rapidtransit440]

I haven't used MySQL for awhile, I had a piece of software that only ran with Postgres and I've never looked back.

InnoDB has a similar feature set and is the lesser of two evils

[TBIKC]

MyISAM already out of date

[mahi]

I prefer InnoDB as it allows to use cascade that keeps integrity and for maintaining security of database. I messed up with on of project using MyISAM.