Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

2 Answers 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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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