Re: HELP! need sparse index for archive engine tables!!!

[Ijaz Rashid]

Hi,

You wrote

>>For archive purpose, you can try MERGE storage engine with compressed MyISAM >>for the old data. Note that compressed MyISAM is read only, so you need to have >>the latest table to be MyISAM for insertion, the rest can be compressed.

MyISAM Insertion speed is 33% less than Archive Engine Speed (SURELY without indexes. It is alot slow with one index).

I can insert data in partitioned Archive table and then convert it to MyISAM table. It doesn't take more time :). Also retrieval from partitioned Archive table is cooool stuf (By using limitation of 1024 partitions on Windows O/S)

#########
BUT CREATION OF ONE INDEX (ID INT, TIME TIMESTAMP) TAKES HOURS for table having 1 BILLION RECORDS :(
#########
COMPRESSION OF indexed MYISAM TABLE TAKES MANY HOURS AS WELL
#########

DURING ALL ABOVE TIME, DATA BECOMES UNAVILIBLE :(

I'm stuck. Plz help me to reduce the time of index creation and compression of 1+ Billion records per day to 5 minutes????


Thank you.
IR