We currently use Innodb and I want to see if the archive engine is an option to replace it. Currently we are finding selects and inserts are getting slow.
We have 31 tables each with 50 million rows together are 1.2 terrabytes of data including an auto increment index on the ID field. Each table represents one of the last 31 days. Once per day we drop one table and start writing to the new table
We only do inserts (batched constantly throughout the day 1000 rows at a time)
We only do select * from table where id in(1,6,36,47,59) etc could be 1000 ids in each select.
My question is could we use the archive engine with auto increment index on the id field and still have fast selects against the primary key without doing full table scans?
I am just not that familar with how the archive engine would work with this type of selects and inserts.
Our main requirements are less storage size and fast insert and selects of the above structure.
We are replacing the old server with a RAID 10 SAN with 10 600gig 15k drives and 48 gig of memory and decent procs
Thanks very much for any advice