Clyde Lewis <cllewis@stripped> 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
