InnoDB or MyISAM: which storage engine, and what to do with an old table

Use InnoDB. It is the default engine of the MariaDB that runs on our hosting, it copes better with failures and simultaneous writes, and it is what current applications expect. MyISAM is an older engine that still turns up in databases created many years ago. If you find MyISAM tables in your site, in most cases they are worth moving to InnoDB.

What each engine does differently

Feature InnoDB MyISAM
Transactions Yes: a group of changes is saved all or nothing. No.
Locking on write Per row: several writes at the same time. Whole table: one write makes the others wait.
Foreign keys Yes. No.
After a failure Recovers by itself, from its internal log. The table may be left marked as damaged and need repair. See a table marked as crashed.
Text search (FULLTEXT) Yes, in current versions. Yes.
Size on disk Tends to take more. Tends to take less.

MyISAM has one small advantage: counting all the rows in a table, with no condition, is instant. It is rare for that to decide anything on a website.

Seeing which engine each table has

1 In cPanel, open phpMyAdmin and pick the database in the left-hand list. See creating databases and using phpMyAdmin.
2 The table list has a column with each table’s type (the engine). Or, on the SQL tab, run:SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE();
3 Note which ones are MyISAM.

Moving a table to InnoDB

1 Make a copy of the database first. See exporting a database with phpMyAdmin. If something goes wrong, you can go back.
2 Pick a quiet moment. The conversion copies the table, and a large table takes a while and stays locked until it finishes.
3 In phpMyAdmin, open the table, go to Operations and, under Storage Engine, choose InnoDB. Or, on the SQL tab, one table at a time:ALTER TABLE table_name ENGINE=InnoDB;
4 Walk through the site: pages, forms, login. Only then convert the next table.
Convert table by table, not all at once, and never without a copy. On a very large database the conversion can exhaust the connection time or take too much space in the account. If you see “MySQL server has gone away” halfway, see the causes, in order of likelihood.
A recent WordPress database is born as InnoDB. It is only worth checking if the site is old, came from another provider or was created by a very old program. If you want to take the chance to lighten the database, see cleaning and optimising the database in phpMyAdmin.

Have old tables and do not want to risk the conversion? Tell us the database and we will go through it with you.

Open a support ticket

SEE ALSO

A table marked as crashed: what it means and how to repair it

Creating databases and using phpMyAdmin

MySQL or MariaDB: which one runs here, and does it matter

Hosting plans and what each includes

RECOMMENDED PRODUCT

Web hosting with cPanel

Domain and SSL included, daily backups and the panel you already know. from $6.59/mo (3-year plan, with coupon)

See plans
  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

Registering a .ao domain: at DNS.AO or through a provider? DNS.AO is the national registry for the .ao zone, under INFOSI. That is where the zone is managed - which is why people...
Hosting in Angola or abroad: what actually changes There are foreign hosts cheaper than any local option, and there is no point hiding it. The question is whether the chea...
Hosting providers in Angola: what to compare before deciding Choosing a provider in Angola has its own difficulty: there are few, and their pages nearly all say the same. These are...
Interweb or AngoHost: comparison and which to choose in Angola AngoHost is one of the better-known Angolan providers. A note on transparency first: their pages are largely built by Ja...
Interweb or SITE AO: comparison and which to choose in Angola SITE AO works mainly on professional website creation in Angola, and also sells hosting and domains. If you need the sit...
PHP or Node.js for your web project: how to choose If you are installing WordPress, a shop or another ready-made program, use PHP: it is what the program asks for. If you...