This is a test server with nothing else hitting the box. I imported a
dump of a production database and is trying to find a way to reduce the
time it takes to rebuild the index on the mentioned table
Hard Drive spec:
Clarion EMC Hard drive 100 GB internal 3.5" Fibre Channel 10000 rpm buffer
CREATE TABLE `parts` (
`prtStore` smallint(5) unsigned NOT NULL default '0',
`prtMMfg` char(3) NOT NULL default '',
`prtMfg` char(3) NOT NULL default '',
`prtPN` char(28) NOT NULL default '',
`prtStripped` char(28) NOT NULL default '',
`prtSort` char(75) NOT NULL default '',
`prtPNID` char(36) NOT NULL default '',
`prtSKU` char(14) NOT NULL default '',
`prtPic` char(20) NOT NULL default '',
`prtDesc` char(40) NOT NULL default '',
`prtDesc2` char(40) NOT NULL default '',
`prtStkQty` int(10) NOT NULL default '0',
`prtRsvQty` int(9) unsigned NOT NULL default '0',
`prtSupFlag` enum('N','Y') NOT NULL default 'N',
`prtAltMfg` char(3) NOT NULL default '',
`prtAltPN` char(28) NOT NULL default '',
`prtVD` tinyint(1) unsigned NOT NULL default '2',
`prtVQ` tinyint(1) unsigned NOT NULL default '0',
`prtMClass` char(1) NOT NULL default '',
`prtSClass1` char(1) NOT NULL default 'N',
`prtSClass2` char(1) NOT NULL default '',
`prtUClass` char(1) NOT NULL default '',
`prtProd` char(3) NOT NULL default '',
`prtGroup` char(10) NOT NULL default '',
`prtRptGroup` char(8) NOT NULL default '',
`prtPNcode` char(3) NOT NULL default '',
`prtDept` char(3) NOT NULL default '',
`prtCat` char(3) NOT NULL default '',
`prtOrderType` char(10) NOT NULL default '',
`prtDNR` enum('N','Y') NOT NULL default 'N',
`prtStockingCode` char(1) NOT NULL default '',
`prtExclude` char(3) NOT NULL default '',
`prtTax` enum('S','T','E') NOT NULL default 'S',
`prtNet` enum('N','Y') NOT NULL default 'N',
`prtMinSell` int(8) unsigned NOT NULL default '0',
`prtPerCar` smallint(5) unsigned NOT NULL default '0',
`prtMinStock` int(9) unsigned NOT NULL default '0',
`prtMaxStock` int(9) unsigned NOT NULL default '0',
`prtOrdPnt` int(9) unsigned NOT NULL default '0',
`prtLeadTime` tinyint(2) unsigned NOT NULL default '0',
`prtSMeasure` char(2) NOT NULL default 'EA',
`prtPMeasure` char(2) NOT NULL default 'EA',
`prtBMeasure` char(2) NOT NULL default 'EA',
`prtPackQty` int(7) unsigned NOT NULL default '0',
`prtPurchQty` smallint(3) unsigned NOT NULL default '1',
`prtMinBuyQty` smallint(3) unsigned NOT NULL default '1',
`prtMaxBuyQty` int(10) unsigned NOT NULL default '0',
`prtWeight` int(9) unsigned NOT NULL default '0',
`prtFreight` int(9) unsigned NOT NULL default '0',
`prtFrghtPPD` enum('Y','N') NOT NULL default 'Y',
`prtOnOrder` int(9) unsigned NOT NULL default '0',
`prtInBound` int(9) unsigned NOT NULL default '0',
`prtWHloc` smallint(5) unsigned NOT NULL default '0',
`prtWHzone` char(3) NOT NULL default '',
`prtVBO` int(9) unsigned NOT NULL default '0',
`prtCBO` int(9) unsigned NOT NULL default '0',
`prtPriVC` char(3) NOT NULL default '',
`prtPriVType` enum('M','S') NOT NULL default 'M',
`prtSecVC` char(3) NOT NULL default '',
`prtSecVType` enum('M','S') NOT NULL default 'M',
`prtStocked` enum('Y','N') NOT NULL default 'Y',
`prtReal` enum('Y','N') NOT NULL default 'Y',
`prtActive` enum('Y','N') NOT NULL default 'Y',
`prtCpnStk` enum('N','Y') NOT NULL default 'N',
`prtCpnVerify` enum('Y','N') NOT NULL default 'Y',
`prtCpnPNID` char(36) NOT NULL default '',
`prtLastSold` date default NULL,
`prtLastReturn` date default NULL,
`prtLastReceived` date default NULL,
`prtLastInventory` datetime default NULL,
`prtCreated` date default NULL,
`prtRetCore` int(10) unsigned NOT NULL default '0',
`prtTotRetCore` int(10) unsigned NOT NULL default '0',
`prtRetDef` int(10) unsigned NOT NULL default '0',
`prtTotRetDef` int(10) unsigned NOT NULL default '0',
`prtRetNew` int(10) unsigned NOT NULL default '0',
`prtTurns` int(9) unsigned NOT NULL default '0',
`prtFixedFee` int(9) unsigned NOT NULL default '0',
`prtFFFlag` enum('N','Y') NOT NULL default 'N',
`prtVOC` int(10) unsigned NOT NULL default '0',
`prtAreaPop` char(3) NOT NULL default '',
`prtMfgPop` char(3) NOT NULL default '',
`prtVehPopulation` int(5) unsigned NOT NULL default '0',
`prtAvgAge` int(6) unsigned NOT NULL default '0',
`prtForecast` int(6) unsigned NOT NULL default '0',
`prtNewPrice1` int(9) unsigned NOT NULL default '0',
`prtNewPrice2` int(9) unsigned NOT NULL default '0',
`prtNewPrice3` int(9) unsigned NOT NULL default '0',
`prtNewPrice4` int(9) unsigned NOT NULL default '0',
`prtNewPrice5` int(9) unsigned NOT NULL default '0',
`prtNewPrice6` int(9) unsigned NOT NULL default '0',
`prtNewPrice7` int(9) unsigned NOT NULL default '0',
`prtNewCurCost` int(9) unsigned NOT NULL default '0',
`prtNewAvgCost` int(9) unsigned NOT NULL default '0',
`prtNewLastCost` int(9) unsigned NOT NULL default '0',
`prtNewVCost1` int(9) unsigned NOT NULL default '0',
`prtNewVCost2` int(9) unsigned NOT NULL default '0',
`prtNewVCost3` int(9) unsigned NOT NULL default '0',
`prtNewVCost4` int(9) unsigned NOT NULL default '0',
`prtNewCoreSell` int(9) unsigned NOT NULL default '0',
`prtNewCoreCost` int(9) unsigned NOT NULL default '0',
`prtOldPrice1` int(9) unsigned NOT NULL default '0',
`prtOldPrice2` int(9) unsigned NOT NULL default '0',
`prtOldPrice3` int(9) unsigned NOT NULL default '0',
`prtOldPrice4` int(9) unsigned NOT NULL default '0',
`prtOldPrice5` int(9) unsigned NOT NULL default '0',
`prtOldPrice6` int(9) unsigned NOT NULL default '0',
`prtOldPrice7` int(9) unsigned NOT NULL default '0',
`prtOldCurCost` int(9) unsigned NOT NULL default '0',
`prtOldAvgCost` int(9) unsigned NOT NULL default '0',
`prtOldLastCost` int(9) unsigned NOT NULL default '0',
`prtOldVCost1` int(9) unsigned NOT NULL default '0',
`prtOldVCost2` int(9) unsigned NOT NULL default '0',
`prtOldVCost3` int(9) unsigned NOT NULL default '0',
`prtOldVCost4` int(9) unsigned NOT NULL default '0',
`prtOldCoreSell` int(9) unsigned NOT NULL default '0',
`prtOldCoreCost` int(9) unsigned NOT NULL default '0',
`prtPrice1` int(9) unsigned NOT NULL default '0',
`prtPrice2` int(9) unsigned NOT NULL default '0',
`prtPrice3` int(9) unsigned NOT NULL default '0',
`prtPrice4` int(9) unsigned NOT NULL default '0',
`prtPrice5` int(9) unsigned NOT NULL default '0',
`prtPrice6` int(9) unsigned NOT NULL default '0',
`prtPrice7` int(9) unsigned NOT NULL default '0',
`prtCurCost` int(9) unsigned NOT NULL default '0',
`prtAvgCost` int(9) unsigned NOT NULL default '0',
`prtLastCost` int(9) unsigned NOT NULL default '0',
`prtVCost1` int(9) unsigned NOT NULL default '0',
`prtVCost2` int(9) unsigned NOT NULL default '0',
`prtVCost3` int(9) unsigned NOT NULL default '0',
`prtVCost4` int(9) unsigned NOT NULL default '0',
`prtCoreSell` int(9) unsigned NOT NULL default '0',
`prtCoreCost` int(9) unsigned NOT NULL default '0',
`prtHstCur` int(10) NOT NULL default '0',
`prtHstYTD` int(10) NOT NULL default '0',
`prtLstYear` int(10) NOT NULL default '0',
`prtDayFill` int(10) NOT NULL default '0',
`prtLostSales` int(10) unsigned NOT NULL default '0',
`prtCurPurch` int(10) NOT NULL default '0',
`prtLifoCost1` int(9) unsigned NOT NULL default '0',
`prtLifoCost2` int(9) unsigned NOT NULL default '0',
`prtLifoCost3` int(9) unsigned NOT NULL default '0',
`prtLifoCost4` int(9) unsigned NOT NULL default '0',
`prtLifoCore1` int(9) unsigned NOT NULL default '0',
`prtLifoCore2` int(9) unsigned NOT NULL default '0',
`prtLifoCore3` int(9) unsigned NOT NULL default '0',
`prtLifoCore4` int(9) unsigned NOT NULL default '0',
`prtDisposalFee` int(9) unsigned NOT NULL default '0',
`prtStkProtect` enum('N','Y') NOT NULL default 'N',
`prtPrChg` datetime default NULL,
`prtPrApply` datetime default NULL,
`prtPrUpdate` enum('N','Y','F','M','I') NOT NULL default 'N',
`prtCommisionPer` int(6) unsigned NOT NULL default '0',
`prtUseCommision` enum('N','Y') NOT NULL default 'N',
`prtRecallMsg` int(10) unsigned NOT NULL default '0',
`prtRelatedSaleMsg` int(10) unsigned NOT NULL default '0',
`prtHazmatMsg` int(10) unsigned NOT NULL default '0',
`prtWarrantyMsg` int(10) unsigned NOT NULL default '0',
`prtWarrantyLength` smallint(5) unsigned NOT NULL default '0',
`prtWarrantyUnit`
enum('NO','MO','DA','YR','WK','HR','LT','LL','ML','TD') NOT NULL default
'NO',
`prtWarrantyMsg2` int(10) unsigned NOT NULL default '0',
`prtWarrantyLength2` smallint(5) unsigned NOT NULL default '0',
`prtWarrantyUnit2`
enum('NO','MO','DA','YR','WK','HR','LT','LL','ML','TD') NOT NULL default
'NO',
`prtInvMod` char(2) NOT NULL default 'S',
`prtBelowCostMargin` int(5) unsigned NOT NULL default '0',
`prtUseBelowCostMargin` enum('N','Y') NOT NULL default 'N',
`prtAboveCostMargin` int(5) unsigned NOT NULL default '0',
`prtUseAboveCostMargin` enum('N','Y') NOT NULL default 'N',
`prtForcedQty` enum('N','Y') NOT NULL default 'N',
`prtIsRestricted` enum('N','Y') NOT NULL default 'N',
`prtHazmatCode` char(3) NOT NULL default '',
`prtShipAnywhere` enum('N','Y') NOT NULL default 'Y',
`prtCInBound` int(11) NOT NULL default '0',
`prtPartTax` char(4) NOT NULL default '',
`prtCoreTax` char(4) NOT NULL default '',
`prtStateFee` char(4) NOT NULL default '',
`prtCIDFee` char(4) NOT NULL default '',
`prtOtherFee` char(4) NOT NULL default '',
`prtDepth` char(18) NOT NULL default '',
`prtHeight` char(9) NOT NULL default '',
`prtWidth` char(9) NOT NULL default '',
`prtMSDS` int(10) unsigned NOT NULL default '0',
`prtRetailCatType` char(3) NOT NULL default '',
`prtLogicOper` enum('N','F','B','L','1') NOT NULL default 'N',
`prtSubType` enum('NONE','TIRE','BATT','PKG','SMPT') NOT NULL default
'NONE',
`prtWrtyTypeID` int(10) unsigned NOT NULL default '0',
`prtSourceCode` char(3) NOT NULL default '',
`prtShopPart` enum('N','Y') NOT NULL default 'N',
`prtPkgGroup` char(10) NOT NULL default '',
`prtPkgSubGroup` char(10) NOT NULL default '',
`prtDisclaimID` int(10) unsigned NOT NULL default '0',
`prtQualifier` char(10) NOT NULL default '',
`prtStatus` enum('ACTIVE','VOID','CHANGEOVER') default NULL,
`prtDCPop` char(5) NOT NULL default '',
`prtCOnOrder` int(10) unsigned NOT NULL default '0',
`prtIsDisplay` enum('N','Y') NOT NULL default 'N',
`prtVolProtect` int(10) unsigned NOT NULL default '0',
`prtDNM` enum('N','Y') NOT NULL default 'N',
`prtLst2Year` int(11) NOT NULL default '0',
`prtLst3Year` int(11) NOT NULL default '0',
`prtDoNotReturn` enum('N','Y') NOT NULL default 'N',
`prtSalesHistTot` int(10) NOT NULL default '0',
`prtSalesHistPrd` smallint(5) NOT NULL default '0',
PRIMARY KEY (`prtPNID`),
UNIQUE KEY `prtUPN` (`prtPN`,`prtStore`,`prtMfg`),
KEY `prtSort` (`prtSort`),
KEY `sku1` (`prtSKU`,`prtStore`),
KEY `prtIPN` (`prtStripped`,`prtStore`,`prtMfg`),
KEY `prtDESC` (`prtDesc`,`prtStore`),
KEY `prtWrtyTypeID` (`prtWrtyTypeID`),
KEY `prtStore` (`prtStore`,`prtMfg`),
CONSTRAINT `parts_f1` FOREIGN KEY (`prtStore`) REFERENCES `cofile`
(`coStore`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Parts Table'
mysql> SHOW VARIABLES LIKE '%buff%';
+-------------------------------+----------+
| Variable_name | Value |
+-------------------------------+----------+
| bdb_log_buffer_size | 1048576 |
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 52428800 |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 67108864 |
| myisam_sort_buffer_size | 16777216 |
| net_buffer_length | 65536 |
| preload_buffer_size | 32768 |
| read_buffer_size | 1044480 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 1048568 |
+-------------------------------+----------+
13 rows in set (0.00 sec)
mysql> SHOW TABLE STATUS LIKE 'parts';
+-------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-------------------------------------------------------------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length |
Data_length | Max_data_length | Index_length | Data_free |
Auto_increment | Create_time | Update_time | Check_time |
Collation | Checksum | Create_options |
Comment
|
+-------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-------------------------------------------------------------------------------+
| parts | InnoDB | 9 | Fixed | 3921279 | 1643 |
6445596672 | NULL | 2941255680 | 0 | NULL
| 2005-08-29 12:32:47 | NULL | NULL | latin1_swedish_ci
| NULL | | Parts Table; InnoDB free: 6144 kB;
(`prtStore`) REFER `por/cofile`(`coStore`) |
+-------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-------------------------------------------------------------------------------+
1 row in set (0.11 sec)
SGreen@stripped wrote:
>
>
> 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
>
