Best MySQL storage engine for read heavy table with two indexes 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!
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.