5

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

|improve this question
  • 1
    Why doesn't the InnoDB section has a "pro: fast" tag? – ypercubeᵀᴹ May 22 '15 at 17:01
  • I think both MEMORY and compressed MyISAM are faster, InnoDB is just relatively slow compared the these two (but I can be wrong) – Zbynek May 22 '15 at 17:03
  • 1
    I'd bet on InnoDB 99.9% of the times. Another disadvantage if you use MyISAM would be that you'd have to optimize settings for 2 engines (myisam + innodb) instead of one (only innodb). – ypercubeᵀᴹ May 22 '15 at 17:05
  • Check this answer from Rolando: dba.stackexchange.com/questions/11099/… – ypercubeᵀᴹ May 22 '15 at 17:09
2

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 !!!

|improve this answer
0
  • "Compressed MyISAM" -- what is that? Maybe "Archive"?
  • Compression is not needed; the tables are too small, and will quickly be fully cached.
  • FOREIGN KEYS -- Why? You have debugged you code, haven't you? There will be no dangling things to check.
  • MEMORY is not unreasonable, if you write 'reload' code that executes whenever you bring up the server, and any updates to the table are writen to both to the MEMORY table an the persistent backup.
  • MyISAM ROW_FORMAT=FIXED -- that's an old wive's tale; use DYNAMIC and VARCHARs.
  • InnoDB's "clustered" PRIMARY KEY makes lookups a little faster than MyISAM for your purpose.
  • Do you know that the lookup tables are causing a noticeable slowdown? I seriously doubt that they are.
  • Do not use lookup tables for "continuous" values, such as FLOATs and DATEs.

I vote for InnoDB without FKs.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.