3

I have an PHP-based API that runs on shared hosting and uses MySQL. I've been doing reading on InnoDB vs MyISAM and wanted to paste some specific things about my API's database to make sure it makes sense to move on to InnoDB. MyISAM was set by default for these tables, so I didn't deliberately pick that database engine.

  1. My queries are a little more writes than reads (70% writes I'd say). Reads/lookups are always by a "foreign key" (userid) (I understand MyISAM doesn't have these constraints) but might be good to know if I move since I could take advantage of that.
  2. I don't do full text searches
  3. My data is important to me, and I recently learned MyISAM has a risk of losing data? A few times in the past I've lost some data and just assumed it was my user's fault in how they interacted with the API. Perhaps not? I am confused about how anyone would be ok with losing data and thus choosing MyISAM so perhaps I don't understand MyISAM enough.
  4. I'm on a shared host and they confirmed I don't have access to change settings in my.cnf, change buffers, threading, concurrency settings, etc.
  5. I will probably switch to DigitalOcean or AWS in the future
  6. My hosting company uses MySQL Version is 14.14 Distribution: 5.6.34

Based on these factors, my instinct is to switch all my tables to InnoDB and at least see if there are problems. If I hit an issue, I can just run the same statement but swap InnoDB with MyISAM to revert back.

Thanks so much.

NullHypothesis
  • 4,286
  • 6
  • 37
  • 79

1 Answers1

3

Short answer: YES! MyISAM was the original format of MySQL, but many years ago InnoDB has been preferred for many reasons. On high-level picture, your app will better perform as InnoDB has a better lock management.

You can find here a longer answer to your question Should I change my DB from MyISAM to InnoDB? (AWS notification) and the following 2 articles covering migration from MyISAM to InnoDB:

https://dba.stackexchange.com/questions/167842/can-converting-myisam-to-innodb-cause-problems

https://kinsta.com/knowledgebase/convert-myisam-to-innodb/

Stephane Paquet
  • 2,315
  • 27
  • 31
  • 1
    [_Another list of conversion tips_](https://mariadb.com/kb/en/mariadb/converting-tables-from-myisam-to-innodb/) – Rick James Jul 23 '17 at 05:36
  • Thanks so much. You don't anticipate issues because I can't change my configuration settings right now and fine tune it? – NullHypothesis Jul 23 '17 at 14:22
  • Until you shrink `key_buffer_size` and increase `innodb_buffer_pool_size`, you will have performance problems. – Rick James Jul 23 '17 at 15:40
  • Issues may happen and are more dependent on the type of column you are using. Recommendation: do a dry run. Migrate your database, but keep your MyISAM system on. Test the new configuration and once you are confident, just turn off the previous system. Don't forget to backup your MyISAM before turning down this system – Stephane Paquet Jul 23 '17 at 15:43
  • Be aware InnoDb hits a limit on the number of varchar/text columns much sooner than MyIsam. – anoldermark Mar 02 '18 at 17:46