Mysql – Optimal engine for small lookup tables in MySQL

database-recommendationinnodbmyisamMySQLstorage-engine

I have following question: I am designing web application with several dozens of small lookup tables: These tables usualy contain three columns (ID, Name, Description) and a couple of rows (mostly fewer than 50, max is about 450).

These lookup tables are expected to change only rarely (they come from standard, which changes once in several years) and will be used only to:

  • fill options in html select
  • be joined to other records in reports

There will be only SELECT statements on these tables 99 % of times, but there will be quite a lot of them.

I am wondering, which database engine would be most efficient to use?

Here are my considerations:

MEMORY

  • pro: very fast
  • con: if the server crashes, all data are lost and need to be recreated
  • con: does not support foreign keys

compressed MyISAM

  • pro: fast
  • con: does not support foreign keys

InnoDB

  • pro: foreign key support

What I would like to ask, is whether there will be significant advantage in using something different than InnoDB – performance-wise

Thanks, Zbynek

Best Answer

I answered a similar question in Aug 2011 : Which DBMS is good for super-fast reads and a simple data structure?

Since you are asking about MySQL and which storage engine. To be honest, it is hard to say because there are rare occasions when MyISAM can outperform InnoDB when it comes to SELECTs.

Here are some of my past posts on this controversy

You might asking right now : Why would I ever favor MyISAM over InnoDB ?

Take a look at this diagram (created by Vadim Tkachenko, Percona CTO)

InnoDB Architecture

MyISAM will cache indexes and your table would have 2 indexes (PRIMARY KEY on id and an index of Name). On the other hand, InnoDB has too many moving parts to accommodate for, especially if the InnoDB Buffer Pool has to load and dismiss 16K pages periodically.

To be fair, you should do an experiment.

This will give you the best assessment for Storage Engine choice for your dataset.

GIVE IT A TRY !!!

Related Question