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?
[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
