I have a database with size about 170GB with rows between 20.000 - 700.000.000 rows (depending on the table). Currently i'm using MyISAM storage engine.

In my case, querying (READ) speed is very important, but transactional is optional.

My CRUD Operation estimates is :

  • 98% READ
  • 2% INSERT
  • < 1% for both UPDATE & DELETE
  • Are the inserts by appending an Auto_increment table (because there are table-lock free rather than other PK based insert)? Keep in mind innodb will need to be tuned its innodb_buffer_pool_size if you deside to transition. Any particular mariadb version?
    – danblack
    Commented Mar 16, 2020 at 6:38
  • No, the primary key isn't from AUTO INCREAMENT. I haven't checked my MariaDB, but it should be latest version since the OS is rolling release.
    – ilos-vigil
    Commented Mar 16, 2020 at 13:08
  • @ilos-vigil - Elaborate on what you mean by that comment on AUTO_INCREMENT. Show us a SHOW CREATE TABLE.
    – Rick James
    Commented Mar 26, 2020 at 23:12

2 Answers 2


Your only real choice here is between InnoDB and Percona's XtraDB engines - see this page.

Coincidentally, I wrote an answer to Change all table ENGINE at once from "MyISAM" to "InnoDB" in MySql with single query this morning on how to convert from MyISAM to InnoDB which might be of interest to you? There are very good reasons for not going with anything non-transactional!

Any particular reason you recommend InnoDB/XtraDB since MariaDB blog recommend Aria instead?

Yes - for their transactional capabilities - you get DRI (Declarative Referential Integrity) thrown in. Enforcing DRI app-side is a disaster waiting to happen - whereas if you use the RDBMS, you have a system that's been tested by literally millions of people. If you "roll-your-own", you have to rewrite it every time you want to write a new app for that particular schema - and if your data is important, this will happen! I would urge you to use either InnoDB or XtraDB but if you do insist on an NTE, then go with Aria! - Vérace

  • btw XtraDB was removed in 10.3. The NTE is "Near Term Extinction" yes?
    – danblack
    Commented Mar 16, 2020 at 6:55

InnoDB, period, fullstop. One caution: The disk space will increase by between 2x and 3x.

This discusses the various things you need to watch out for: http://mysql.rjweb.org/doc.php/myisam2innodb . And it provides the desired ALTERs.

Do change the settings. Most importantly is to decrease key_buffer_size and increase innodb_buffer_pool_size. (See my link.)

"Read speed" -- Oracle has been "proving" for the last few releases that InnoDB is faster. Because of the "clustering" of the PK in InnoDB, it may be better to make a few changes to the indexes (primary and secondary).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.