MySQL MyISAM vs InnoDB

[tombman]

hi,
When is it appropriate to use InnoDB table types?It is not easy just to say use "MyISAM or InnoDB";Would it make sense to mix and match MyISAM and InnoDB table types in my database based on the number of rows in the table?MyISAM memory and space usage, full text support, bulk insert capabilities and speed are known plus factors.

[Zecho]

hi,
First it is worth to note MySQL only saves very basic cardinality information for index prefixes for index stats and these rarely change. There is no histograms or any other skew metrics etc. MySQL optimizer also uses number of rows in the table for many decisions but this is computed live (maintained for MyISAM and estimated during query execution for Innodb). This basic information means it does not change whole that quickly at extent to affect optimizer plans.

[opaper]

To see what engine your existing table are, run:
show table status;
Engine is listed second.

MyISAM has been the default storage engine since 3.23. To see what your server is setup to use for the default engine, run:
show variables like 'storage_engine';

[MindSpace]

MyISAM in most cases will be faster than InnoDB for run of the mill sort of work. Selecting, updating and inserting are all very speedy under normal circumstances. It is the default engine chosen by the MySQL development team which speaks to its integrity, reliability, and performance.

InnoDB, or the OSX of the database-engine world, has emerged with some nifty features and created a niche for itself very quickly. Boasting features like row-level locking, transaction-safe queries, and relational table design are all very temping. The first two features really shine in a table that is constantly getting hammered like a logs, or search engine-type table. Since queries happen in the blink of an eye (faster actually) table-level locking(MyISAM) is sufficient in most other normal cases.

InnoDB recovers from a crash or other unexpected shutdown by replaying its logs. MyISAM must fully scan and repair or rebuild any indexes or possibly tables which had been updated but not fully flushed to disk.

[Reegan]

There are major advantages to INNO that far overshadow negatives you might come up with. First and most importantly is row level locking. You get this regardless of coding and it's the cat's ass. There is simple no way to run anything but a modest site on anything but. With table locking such as what MyISAM uses the entire table is locked for updates. INSERTs are ok but each time a record is updated it locks the entire table. It may not sound like there are many updates but each time a topic is viewed the view count gets updated. That's a lot of updates on a busy site. Also on a busy site those locks quickly spiral out of control causing meltdown. There is no deadlock, there are simply more requests for locks coming in than can be handled so the lock count becomes ever increasing, the site slows to a crawl and then crashes. This doesn't happen with INNODB. My site would last all of 3 minutes before becoming totally unresponsive without it.

The second major advantage of INNODB vs. MyISAM is reliability. INNO simply doesn't corrupt. I've been running my site for 4 years first on VB 1, then 2 then IPB 1.3 without ever having a database table or index corruption. It simply doesn't happen, at all.

[Katty]

MyISAM tables are the default table type used by MySQL.

The main difference between MyISAM and InnoDB tables is that InnoDB tables support transactions and MyISAM tables don't. Unless you need transaction support in your database, you should always use MyISAM tables.

MyISAM creates slightly smaller tables and require less space than InnoDB tables, and you can optimise them for space/speed using the myisamchk tool, but InnoDB allows more features, especially foreign key contraints.