MySQL Bugs: #24663: Possiblity of indexes for ARCHIVE engine

Bug #24663	Possiblity of indexes for ARCHIVE engine
Submitted:	28 Nov 2006 17:15	Modified:	2 Sep 2009 7:55
Reporter:	Thorben Jändling	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Archive storage engine	Severity:	S4 (Feature request)
Version:	5	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Hi,

We're using mysql to store netflow data using myisam tables.
Data is insterted once (1 table per hour)*, indexed*, queried (a lot) and eventually the table/hour is dropped (data aging).

*We do just one load infile, which automatically disables keys and then re-enables them after.

myisam was choosen for speed, but io (RAID0 over an UltraSCSI320) is a major bottle neck, while CPU usages is very low.

A table (hour) has about 185,746,530 rows, the data size is 10.55G, with indexes it is 17.62G. (Due to IO restrictions we do not pack the myisam tables)

Recently I have noticed the new ARCHIVE engine in MySQL 5, which would meet our needs a lot better if it wasn't for the fact it didn't have any indexes. It would reduce IO (we have plenty of CPU/RAM resources) and allow us to keep a longer history before disk space is consumed.

However without any indexes queries take too long.

I would imagine that once written a rows on disk (compressed) position does not change. So:

Would it be possible to add index support?
Is index support planned?
If so, on what sort of time scale?

How to repeat:
n/a feature request

Suggested fix:
Please implement indexing for ARCHIVE engine.

Thank you for the feature request.

This is partially done: since version 5.1 ARCHIVE supports AUTO_INCREMENT. See http://dev.mysql.com/doc/refman/5.1/en/archive-storage-engine.html for details.