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.
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.
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.
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.
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.
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.
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
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.
