Clyde Lewis <[EMAIL PROTECTED]> wrote on 08/29/2005 01:47:11 PM: > Guys, > > I have a huge table to which I'm attempting to update the foreign key > and index. It is taking me more than 20 hrs to complete the process and > would like to know if someone can point me in the right direction. > Please let me know of any additional information that I should provide. > > > mysql> show index from parts; > +-------+------------+---------------+--------------+--------------- > +-----------+-------------+----------+--------+------+------------+---------+
> > | Table | Non_unique | Key_name | Seq_in_index | Column_name | > Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | > +-------+------------+---------------+--------------+--------------- > +-----------+-------------+----------+--------+------+------------+---------+ > > | parts | 0 | PRIMARY | 1 | prtPNID | > A | 3921279 | NULL | NULL | | BTREE | | > | parts | 0 | prtUPN | 1 | prtPN | > A | 326773 | NULL | NULL | | BTREE | | > | parts | 0 | prtUPN | 2 | prtStore | > A | 3921279 | NULL | NULL | | BTREE | | > | parts | 0 | prtUPN | 3 | prtMfg | > A | 3921279 | NULL | NULL | | BTREE | | > | parts | 1 | prtSort | 1 | prtSort | > A | 3921279 | NULL | NULL | | BTREE | | > | parts | 1 | sku1 | 1 | prtSKU | > A | 18 | NULL | NULL | | BTREE | | > | parts | 1 | sku1 | 2 | prtStore | > A | 18 | NULL | NULL | | BTREE | | > | parts | 1 | prtIPN | 1 | prtStripped | > A | 230663 | NULL | NULL | | BTREE | | > | parts | 1 | prtIPN | 2 | prtStore | > A | 3921279 | NULL | NULL | | BTREE | | > | parts | 1 | prtIPN | 3 | prtMfg | > A | 3921279 | NULL | NULL | | BTREE | | > | parts | 1 | prtDESC | 1 | prtDesc | > A | 301636 | NULL | NULL | | BTREE | | > | parts | 1 | prtDESC | 2 | prtStore | > A | 3921279 | NULL | NULL | | BTREE | | > | parts | 1 | prtWrtyTypeID | 1 | prtWrtyTypeID | > A | 18 | NULL | NULL | | BTREE | | > | parts | 1 | prtStore | 1 | prtStore | > A | 18 | NULL | NULL | | BTREE | | > | parts | 1 | prtStore | 2 | prtMfg | > A | 46132 | NULL | NULL | | BTREE | | > +-------+------------+---------------+--------------+--------------- > +-----------+-------------+----------+--------+------+------------+---------+ > > > > mysql> explain select * from parts; > +----+-------------+-------+------+---------------+------+--------- > +------+---------+-------+ > > | id | select_type | table | type | possible_keys | key | key_len | > ref | rows | Extra | > +----+-------------+-------+------+---------------+------+--------- > +------+---------+-------+ > > | 1 | SIMPLE | parts | ALL | NULL | NULL | NULL | > NULL | 3921279 | | > +----+-------------+-------+------+---------------+------+--------- > +------+---------+-------+ > > 1 row in set (0.00 sec) > > Files from mysqldata directory > -rw-rw---- 1 mysql mysql 18K Aug 25 17:24 parts.frm > -rw-rw---- 1 mysql mysql 8.9G Aug 29 12:32 parts.ibd > > > DB MySQL: 4.1.11 > OS: Solaris 9 > Hardware: SUN 2900; 32GB RAM > > Would you be so kind as to also provide the results of SHOW CREATE TABLE parts\G and SHOW VARIABLES LIKE '%buff%'; and SHOW TABLE STATUS LIKE 'parts'\G Based on the sizes of your fields, you may just have a lot of data to index. The buffer settings will tell us how much space you have allocated for sorting and key creation. How fast are your disks and what else is using them right now? Make sure your anti-virus scanner is not checking every write from mysql to your data directory (that can really slow you down, especially during an index rebuild). Please respond to the whole list and not just to me. I have a busy afternoon and may not be able to get back to you very soon. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
