If you are doing big updates on indexes, like during an import, you
want to adjust your variables accordingly. The variables that would
speed inserts and index updates are
key_buffer_size
bulk_insert_buffer_size
if using MYISAM table types:
myisam_max_sort_file_size
myisam_sort_buffer_size
Read this part of the online manual:
http://dev.mysql.com/doc/mysql/en/insert-speed.html
Tuning your variables specifically for importing can give you a
dramatic performance boost. Then set them back to production values
when you are done.
On Aug 29, 2005, at 1:47 PM, Clyde Lewis wrote:
> 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
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?
> unsub=brent@stripped
>
>
>
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
