Best MySQL storage engine for read heavy table with two indexes

Question

I have a pretty big MySQL table (70G) with about 15 columns and 1.5M rows.
There is one index on the autoincrement id and one on a foreign key (integer). Apart from that there are several VARCHAR and TEXT fields.

The use case is as follows: The data is inserted once and then read several times (SELECT count(*) FROM table WHERE foreign_key = 1). Every day rows that are older than 3 months are deleted.

I have thought of using ARCHIVE as storage engine but have read that it has many downsides and basically only one upside - saving disk space.

What would be the preferred storage engine for this use case?

Thanks!

Answer 1

The biggest downside for ARCHIVE in your environment would be that it won't work.

The ARCHIVE storage engine only supports INSERT and SELECT -- not UPDATE or DELETE -- and only supports one index, on the primary key.

I would to go with InnoDB, which can, incidentally, also store tables compressed on disk. I can't think of a good reason to go any other way.

Answer 2

If it's all about reading rows and there are no transactions involved and at the same time you don't really care much about loss of few rows, then Myisam is what I will suggest.

It's pretty awesome when it comes to mainly reading part.