CODEWITHSUNDEEP
mysqldatabaseinnodbmyisam
NullHypothesis
NullHypothesis

Reputation: 4516

Should I switch to InnoDB for my tables

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.

Upvotes: 3

Views: 1369

Answers (1)

Stephane Paquet
Stephane Paquet

Reputation: 2344

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/

Upvotes: 3

Related Questions