Can i use some tables with InnoDB engine and some with MyIsam on my MySQL database?

0
votes

I read that Innodb is better to use on a table that get a lot's of insert records simultaneously. My application gets about 50 records per seconds. So for these tables should I use Innodb, right?

In the other hand i have some tables that are only used for select, they get few updated or have few new insert. Is MyIsam faster for select ? If it's the case, is it better to leave some table with MyIsam and some with Innodb or should i use all tables with the same engine ?

My application also searches a lot on the tables that i want to pass in Innodb. What should i do ?

3
mysqlinnodbmyisam

3 Answers

2
votes

you can check these:

Reasons to use MyISAM:

  • Tables are really fast for select-heavy loads
  • Table level locks limit their scalability for write intensive multi-user environments.
  • Smallest disk space consumption
  • Fulltext index
  • Merged and compressed tables.

Reasons to use InnoDB:

  • ACID transactions
  • Row level locking
  • Consistent reads – allows you to reach excellent read write concurrency.
  • Primary key clustering – gives excellent performance in some cases.
  • Foreign key support.
  • Both index and data pages can be cached.
  • Automatic crash recovery – in case MySQL shutdown was unclean InnoDB tables will still
  • recover to the consistent state- No check repair like MyISAM may require. All updates have to pass through transactional engine in
    InnoDB, which often decreases - performance compared to
    non-transactional storage engines.

quoted from here

and for the last part:

REMEMBER! It's OK to mix table types in the same database! In fact it's recommended and frequently required. However, it is important to note that if you are having performance issues when joining the two types, try converting one to the other and see if that fixes it. This issue does not happen often but it has been reported.

quoted from here

I hope that's enough :D

1
votes

Yes you can, but I'd go with InnoDB only unless there is some serious performance bottleneck

same question on SO

MySQL forum

1
votes

In short yes you can mix and match to your hearts content.

Keep the following in mind:

InnoDB is ACID complaint. Thus is you need any ACID features use InnoDB. MyISAM is does not support a lot of things like foreign key constraints for example.

Now speed is hard to quantify exactly. Depending on execution paths you might get very big or very small speed differences.

Test and check there is no right or wrong answer here.