0

I am using Spring framework to develop a web app and I use Hibernate to support the persistent layer. I already know that I can custom my own dialect with the codes below:

public class MySQL5Dialect extends MySQL5InnoDBDialect {

    @Override
    public String getTableTypeString() {
        return " ENGINE=InnoDB DEFAULT CHARSET=utf8";
    }
}

But this applies to all the tables. Now I have different tables with different MySQL storage engines. For example, one table uses InnoDB and the other uses MyISAM. How do I accomplish this? Thanks in advance.

erlengzi
  • 359
  • 4
  • 15
  • A main gain of using hibernate is that your database details are hidden from the developer - making it possibly easier to migrate between database vendors or versions. If you're that deep into the database you might want to consider other techniques of seting up your tables (like flyway) and then map your hibernate to the (manually) created tables? – Jan May 03 '17 at 12:24
  • @Jan Thanks. But if I don't want to manually create tables using native sql and my database is definitely MySQL. Isn't there a way to create tables with different engines? – erlengzi May 03 '17 at 12:39
  • There is no good reason to create any new table in anything other than InnoDB. (If you think there is, we can discuss that.) – Rick James May 03 '17 at 14:11
  • @RickJames The 'select' operation is faster in MyISAM than InnoDB, isn't it? And here is a link: http://stackoverflow.com/questions/20148/myisam-versus-innodb – erlengzi May 03 '17 at 15:57
  • @StevenLiu - Notice the date: 2008. The dramatic graph was for the one case where MyISAM wins: `COUNT(*)` _without_ a `WHERE` clause. If you run _concurrency_ test, InnoDB is very likely to win, _even against MEMORY_. Note the version numbers: Most Answers are 2 major version behind. Shall I go on? – Rick James May 03 '17 at 17:52
  • @StevenLiu - I did go on. -- I provided another answer to _that_ Question. – Rick James May 03 '17 at 18:28

1 Answers1

0

Some questions I feel compelled to answer by backing up a step and questioning the premise.

The premise is that you want to use different engines? The response is -- Use only InnoDB.

Your counter is -- But isn't MyISAM faster?

The answer is -- That's an old wives' tale. It is very likely that your application (whatever it is) will run faster with all InnoDB than with any MyISAM.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • If I use only InnoDB, the primary key of all tables should be "AUTO_INCREMENT" and should not be very long according to its clustered B-tree index, is that right? If I want to use a UUID as primary key, should I change to MyISAM? – erlengzi May 04 '17 at 01:45
  • UUIDs are performance killers for very large tables (in any Engine). MyISAM is going away; there is virtually no good reason to change to it. – Rick James May 04 '17 at 04:52