Storage engine for high volume of selects

Question

Background

I am creating an API utilizing the Bible where I would like to be able to eliminate as much as the database bottleneck as possible. My data is fairly de-normalised to eliminate most unnecessary joins.

Information

Seeing as the text of the Bible doesn't change, I will be doing hardly any INSERT statements. The only time I will insert data is when I add a new translation, which will happen periodically, but I don't care about the speed here.

I will, however, be doing tons of SELECT statements.

I do not need any transnational, ACID compliant features. My primary concern is speed.

The Question

What would the ideal MySql storage engine be to fit these conditions?

I am aware of the basics of each engine (my guess would that MyISAM is ideal), so I am looking for an answer that can be backed up with statistics or further reasoning demonstrating a deep knowledge of some of these engines.

Although using NoSQL may be better than a RDBMS, that is not the information I'm looking for.

Answer 1

the bible is small in terms of file size. and as you said doesnt change.

For the best performance on reads consider Memory. This has the limitation that you cant use text / blob. But providing your data is split into 65,533 char chunks you will be fine.

http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html

Using memory also means if power is lost / server is restarted all data is lost. so periodically writing to disk will be useful and on restart you will need to populate the table again.

You will need extra RAM to use this method over other methods though as all tables are stored in RAM

From the question in the comments.

The docs say

To populate a MEMORY table when the MySQL server starts, you can use the --init-file option. For example, you can put statements such as INSERT INTO ... SELECT or LOAD DATA INFILE into this file to load the table from a persistent data source. See Section 5.1.3, “Server Command Options”, and Section 13.2.6, “LOAD DATA INFILE Syntax”.

http://dev.mysql.com/doc/refman/5.5/en/memory-storage-engine.html#idp82809968

http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_init-file

Again you will need to keep this file up to date with any changes. (can use a mysqldump to maintain it)

Answer 2

Innodb with good indexes maybe even good partitions. innodb is designed to have better performance with multible threads clients (read more clients at the same time) vs MyISAM is not build for that.

if the server is correcly configured Innodb will really blast away myisam on performance