Pros and Cons of the MySQL Archive Storage Engine?

Question

For a website I am developing, I am going to store user activity information (like Facebook) and HTTP requests to the server into a MySQL database.

I was going to use InnoDB as the storage engine, however, I briefly read about the Archive storage engine and this sounds like the way forward for information which is only Inserted or Selected and never Updated or Deleted.

Before I proceed, I would like to know the any pros and cons to help me make a decision whether to use it or not.

Furrier

Also, If you've used or are currently using the Archive storage engine. I would love to hear from you. – Furrier

I'm currently feeding years of apache logfiles into the database so I can run some queries. I'm still at the beginning of 2007 (from one server) and already have ~170 million rows in 31GB. Were you able to use it yet? Btw, is that Michael Schumacher in your picture?? :-p – Briggs

I tried archive on MySQL 5.0 and it crashed the MySQL server once I reached 2GB (Debian etch). Will try with Lenny .... – Briggs

Answer 1

We are not currently using the Archive storage engine, but have done some research on it. One requirement of our application is that it stores lots of historical information for long periods of time. That data is never updated, but needs to be selected occasionally.

So ... with that in mind:

PROs

Very small footprint. Uses zlib for data compression.

More efficient/quicker backups due to small data file size.

CONs

Does not allow column indexes. Selects require a full table scan.

Does not support spatial data.

--

It is likely that we will not use this storage engine due to the indexing limitations.

Cliftonclim