Ok in a MySQL 5.0.51a database we have the following table:
| PricingArchive | CREATE TABLE `PricingArchive` (
`SecurityId` varchar(12) NOT NULL,
`Vendor` varchar(4) NOT NULL,
`PriceDate` date NOT NULL default '0000-00-00',
`LastModifiedTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`ClosePrice` decimal(16,9) NOT NULL,
`LowPrice` decimal(16,9) default NULL,
`HighPrice` decimal(16,9) default NULL,
`Volume` varchar(10) default NULL,
`Exchange` varchar(4) NOT NULL,
PRIMARY KEY (`SecurityId`,`Vendor`,`PriceDate`,`Exchange`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
So thus this table has a Primary Key Index and is using 4 columns to do it's work to get the data integrirty correct.
The table count is the following:
mysql> select count(*) from PricingArchive;
| count(*) |
| 402488796 |
1 row in set (0.01 sec)
So last week we had a space issue on the server that this database resides, and I ran the optimize command on each of the 19 tables that exist in the schema. Now in the case of this table it was huge and for some reason the index was 10 times larger than the table itself.
Thus the table was like 42 gig with the index being 10 times larger. So all that is going on is a process nightly inserts new rows into the table, there are almost NO Deletes or Updates so why would this index grow so HUGE?
Next the optimize command ran for 4.5 hours and still didn't complete, so I killed the process off on the server and used the PID to kill it within the database.
Next since no work was being done, I dropped the two indexes on this table, then ran the optimize command and it completed it's work in like 10 minutes...instead of the 4.5 hours before that I had to kill.
Then I added the Primary Key index back on using the following command:
Mysql> alter table PricingArchive add PRIMARY KEY (`SecurityId`,`Vendor`,`PriceDate`,`Exchange`);
Now this took between 24 and 36 hours to complete.
We still need to add the 2nd index back on as well, but I am waiting to do this on the weekend so it has no effect on processes on that server or in that database...
So the syntax for that is:
Alter table PricingArchive add index `index_PriceDate` (`PriceDate`);
So I guess my question here is there a better more effective or efficient way to index this table? Is the 4 column Primary Key the best way to make this work. Also this table is MyISAM instead of INNODB storage.
Now I should say that every 8-9 months this index grows HUGE and we must repeat this process to shrink/reclaim the space on this box. This time it went from 96% used back to 25% used ...it got up to 96% from the listed 90% below:
pool0/mysql 667G 545G 64G 90% /var/lib/mysql