Mysql – When MyISAM is better than InnoDB

innodbmyisamMySQL

Sometimes I got asked on some interviews: what benefits does InnoDB have against MyISAM and when MyISAM is better than InnoDB? It's all clear about the first part of question: InnoDB is transaction compliant, row-level blocking instead of table-level blocking, foreign key support and some others, these points just came to mind immidiately.

But when MyISAM is really better than InnoDB?

Best Answer

MyISAM is better than InnoDB when you don't need those advanced features and storage speed is more important than other concerns. MyISAM also allows full-text searches to be performed inside the database engine itself, instead of needing to query results and then search them as an array or whatever in your application.

InnoDB is a reasonable choice if you need to store data with a high degree of fidelity with complicated interactions and relationships. MyISAM is a reasonable choice if you need to save or load a large number of records in a small amount of time.

I wouldn't recommend using MyISAM for data that matters. It's great for logging or comments fields or anything where you don't particularly care if a record vanishes into the twisting nether. InnoDB is good for when you care about your data, don't need fast searches and have to use MySQL.

It's also worth mentioning that InnoDB supports row-level locking, while MyISAM only supports table-level locking - which is to say that for many common situations, InnoDB can be dramatically faster due to more queries executing in parallel.

The bottom line: Use InnoDB unless you absolutely have to use MyISAM. Alternatively, develop against PostgreSQL and get the best of both.

Related Solutions

Mysql – Am I right that InnoDb is better for frequent concurrent updates and inserts than MyISAM

MyISAM is faster for reads and writes, but not at the same time. In other words, if you need to do a lot of writes, it will be faster, but if you want to also have people reading at the same time the readers and the writers will block each other and you may have concurrency issues. You should use InnoDB in such scenarios, and most mysql gurus recommend using InnoDB by default anyway because it's considered more generally reliable than MyISAM, despite being slower under some use cases.

Mysql – How to convert all tables from MyISAM into InnoDB

Run this SQL statement (in the MySQL client, phpMyAdmin, or wherever) to retrieve all the MyISAM tables in your database.

Replace value of the name_of_your_db variable with your database name.

SET @DATABASE_NAME = 'name_of_your_db';

SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

Then, copy the output and run as a new SQL query.

Best Answer

Related Solutions

Mysql – Am I right that InnoDb is better for frequent concurrent updates and inserts than MyISAM

Mysql – How to convert all tables from MyISAM into InnoDB

Related Topic