#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103

    Issue with a Multi column Primary Key


    Hello everyone,

    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

    Thoughts anyone?
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103
    Now I guess the best way to recover space would be to use the msqlcheck

    Like to combine Optimize and repair for the schema in question I could then run:

    mysqlcheck -u root -p --auto-repair -c -o PricingGeneration

    that would have done what I needed for the issue of reclaiming the space, but still what is the best way to effectively index this above table
  4. #3
  5. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    Are you running queries against SecurityId, or could you just change the primary key to (`PriceDate`,`SecurityId`,`Vendor`,`Exchange`) and forgo the need for the second index?
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103
    Yes there are many different selects, one if specific that just does the querry verses the PriceDate and that is why we have the extra Index as well.
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103
    Sorry a double post!!!

IMN logo majestic logo threadwatch logo seochat tools logo