MySQL Forums
Forum List  »  Performance

Too much time in Copy to Tmp Table
Posted by: Ricardo Manfrinato
Date: October 01, 2015 11:55AM

I am having several occurrences of this query in 'Copying to tmp table' State and a High CPU Load.

System: RHEL 6 - MEM 48GB RAM - CPU 12 cores

Query in question
SELECT 
    COUNT(*) AS y0_
FROM
    Supplier this_
WHERE
    this_.SupplierSN IN (SELECT DISTINCT
            supplierin1_.SupplierSN AS y0_
        FROM
            SupplierProvisioning this_
                INNER JOIN
            Supplier supplierin1_ ON this_.SupplierSN = supplierin1_.SupplierSN
                INNER JOIN
            CatalogProvisioning supplierin5_ ON supplierin1_.SupplierSN = supplierin5_.SupplierSN
                INNER JOIN
            ProductCatalog supplierin6_ ON supplierin5_.ProductCatalogSN = supplierin6_.ProductCatalogSN
                INNER JOIN
            CatalogUsage supplierin7_ ON supplierin6_.ProductCatalogSN = supplierin7_.ProductCatalogSN
                INNER JOIN
            ContractProvisioning supplierin2_ ON supplierin1_.SupplierSN = supplierin2_.SupplierSN
                INNER JOIN
            Contract supplierin3_ ON supplierin2_.ContractSN = supplierin3_.ContractSN
                INNER JOIN
            ContractUsage supplierin4_ ON supplierin3_.ContractSN = supplierin4_.ContractSN
        WHERE
            this_.CustomerSN = supplierin4_.CustomerSN
                AND this_.CustomerSN = supplierin7_.CustomerSN
                AND this_.CustomerSN IN (1)
                AND (supplierin3_.ContractSN IN (SELECT DISTINCT
                    userentitl1_.ContractSN AS y0_
                FROM
                    Entitlement this_
                        INNER JOIN
                    Contract userentitl1_ ON this_.EntitledContract = userentitl1_.ContractSN
                WHERE
                    this_.RefererType = 'Contract'
                        AND ((this_.UserID = 'xprs128@v25db114.mkm.can.com'
                        AND this_.CountryID IS NULL)
                        OR this_.UserID IS NULL)
                        AND (this_.CountryID = 'US'
                        OR this_.CountryID IS NULL)
                        AND (this_.Company = '0147'
                        OR this_.Company IS NULL)
                        AND (this_.WorkLocation = '1501'
                        OR this_.WorkLocation IS NULL)
                        AND (this_.BusinessUnit = 'BU00'
                        OR this_.BusinessUnit IS NULL)
                        AND (this_.CostCenter = 'C6YC'
                        OR this_.CostCenter IS NULL)
                        AND this_.PurchaseType IS NULL
                        AND this_.ProjectCode IS NULL
                        AND this_.CustomerID IS NULL
                        AND this_.SupplierID IS NULL
                        AND this_.ClassificationID IS NULL
                        AND this_.ClassificationGroupID IS NULL
                        AND this_.CatalogID IS NULL
                        AND this_.ProductID IS NULL
                        AND this_.ContractId IS NULL)
                AND supplierin3_.ContractSN IN (SELECT DISTINCT
                    userpurcha1_.ContractSN AS y0_
                FROM
                    Entitlement this_
                        INNER JOIN
                    Contract userpurcha1_ ON this_.EntitledContract = userpurcha1_.ContractSN
                WHERE
                    this_.RefererType = 'Contract'
                        AND (this_.PurchaseType = 'Internal'
                        OR this_.PurchaseType = 'InternalOnly')))
                AND ((supplierin3_.ValidFrom IS NULL
                OR supplierin3_.ValidFrom < '2015-10-01 15:57:49')
                AND (supplierin3_.ValidTo IS NULL
                OR supplierin3_.ValidTo > '2015-10-01 15:57:49')))
                
                
Query Explain
id	select_type	         table	       type   possible_keys	                                       key               key_len  ref         rows  filtered	Extra
--------------------------------------------------------------------------------------------------------------------------------------------------------------
1	PRIMARY	            this_	       index  NULL                                                   FK000000000000270	9        NULL	      19122	100      Using where; Using index
2	DEPENDENT SUBQUERY	this_	       eq_ref UK000000000000060,FK000000000000287,FK000000000000289	UK000000000000060	16	      const,func	1     100      Using index; Using temporary
2	DEPENDENT SUBQUERY	supplierin1_ eq_ref PRIMARY	                                             PRIMARY           8        func        1     100      Using where; Using index
2	DEPENDENT SUBQUERY	supplierin5_ ref    UK000000000000009,FK000000000000032,FK000000000000031	FK000000000000031	8	      func        2     100      Using where; Distinct
2	DEPENDENT SUBQUERY	supplierin7_ ref    UK000000000000010,FK000000000000033,FK000000000000034	UK000000000000010	17	      test.supplierin5_.ProductCatalogSN,
                                                                                                                              const	      1     100      Using where; Using index; Distinct
2	DEPENDENT SUBQUERY	supplierin6_ eq_ref PRIMARY                                                PRIMARY           8	      test.supplierin7_.ProductCatalogSN	1	100	Using where; Using index; Distinct
2	DEPENDENT SUBQUERY	supplierin2_ ref    UK000000000000021,FK000000000000081,FK000000000000080	FK000000000000080	8        func        2     100      Using where; Distinct
2	DEPENDENT SUBQUERY	supplierin4_ ref    UK000000000000022,FK000000000000082,FK000000000000083	UK000000000000022	17	      test.supplierin2_.ContractSN,
                                                                                                                              const	      1     100      Using where; Using index; Distinct
2	DEPENDENT SUBQUERY	supplierin3_ eq_ref PRIMARY                                                PRIMARY           8        test.supplierin4_.ContractSN	
                                                                                                                                          1     100      Using where; Distinct
4	DEPENDENT SUBQUERY	userpurcha1_ eq_ref PRIMARY                                                PRIMARY           8        func	      1     100      Using index; Using temporary
4	DEPENDENT SUBQUERY	this_	       ref    FK000000000001184,Entitlement_idx_customer,            FK000000000001184	9        func        2     100      Using where
                                            Entitlement_idx_product,Entitlement_idx_supplier,
                                            Entitlement_idx_classification,
                                            Entitlement_idx_contract	                              
3	DEPENDENT SUBQUERY	userentitl1_ eq_ref PRIMARY                                                PRIMARY           8        func        1     100      Using index; Using temporary
3	DEPENDENT SUBQUERY	this_	       ref    FK000000000001184,Entitlement_idx_customer,            FK000000000001184	9        func        2     100      Using where
                                            Entitlement_idx_product,Entitlement_idx_supplier,
                                            Entitlement_idx_classification,
                                            Entitlement_idx_contract	                              

Tables

Catalogprovisioning
CREATE TABLE `catalogprovisioning` (
  `CatalogProvisioningSN` bigint(20) NOT NULL AUTO_INCREMENT,
  `ProductCatalogSN` bigint(20) NOT NULL,
  `SupplierSN` bigint(20) NOT NULL,
  `CurrentVersion` int(11) DEFAULT NULL,
  `ReleasedVersion` int(11) DEFAULT NULL,
  `StatusSN` bigint(20) NOT NULL,
  `CreatedBy` varchar(60) COLLATE utf8_bin NOT NULL,
  `CreatedOn` datetime NOT NULL,
  `ChangedBy` varchar(60) COLLATE utf8_bin NOT NULL,
  `ChangedOn` datetime NOT NULL,
  PRIMARY KEY (`CatalogProvisioningSN`),
  UNIQUE KEY `UK000000000000009` (`ProductCatalogSN`,`SupplierSN`),
  KEY `FK000000000000030` (`StatusSN`),
  KEY `FK000000000000032` (`ProductCatalogSN`),
  KEY `FK000000000000031` (`SupplierSN`),
  CONSTRAINT `FK000000000000030` FOREIGN KEY (`StatusSN`) REFERENCES `status` (`StatusSN`),
  CONSTRAINT `FK000000000000031` FOREIGN KEY (`SupplierSN`) REFERENCES `supplier` (`SupplierSN`),
  CONSTRAINT `FK000000000000032` FOREIGN KEY (`ProductCatalogSN`) REFERENCES `productcatalog` (`ProductCatalogSN`)
) ENGINE=InnoDB AUTO_INCREMENT=46869 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CatalogUsage
CREATE TABLE `catalogusage` (
  `CatalogUsageSN` bigint(20) NOT NULL AUTO_INCREMENT,
  `ProductCatalogSN` bigint(20) NOT NULL,
  `CustomerSN` bigint(20) DEFAULT NULL,
  `CreatedBy` varchar(60) COLLATE utf8_bin NOT NULL,
  `CreatedOn` datetime NOT NULL,
  `ChangedBy` varchar(60) COLLATE utf8_bin NOT NULL,
  `ChangedOn` datetime NOT NULL,
  PRIMARY KEY (`CatalogUsageSN`),
  UNIQUE KEY `UK000000000000010` (`ProductCatalogSN`,`CustomerSN`),
  KEY `FK000000000000033` (`ProductCatalogSN`),
  KEY `FK000000000000034` (`CustomerSN`),
  CONSTRAINT `FK000000000000033` FOREIGN KEY (`ProductCatalogSN`) REFERENCES `productcatalog` (`ProductCatalogSN`),
  CONSTRAINT `FK000000000000034` FOREIGN KEY (`CustomerSN`) REFERENCES `customer` (`CustomerSN`)
) ENGINE=InnoDB AUTO_INCREMENT=45407 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Contract 
CREATE TABLE `contract` (
  `ContractSN` bigint(20) NOT NULL AUTO_INCREMENT,
  `ContractID` varchar(100) COLLATE utf8_bin NOT NULL,
  `ValidFrom` datetime DEFAULT NULL,
  `ValidTo` datetime DEFAULT NULL,
  `StatusSN` bigint(20) NOT NULL,
  `HierarchyCode` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `ExtContractID` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `ExtContractLineID` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `Description` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `Type` varchar(4) COLLATE utf8_bin DEFAULT NULL,
  `isPreferred` char(1) COLLATE utf8_bin DEFAULT NULL,
  `NumberOfPrices` int(11) DEFAULT NULL,
  `ParentContractSN` bigint(20) DEFAULT NULL,
  `IsStandard` char(1) COLLATE utf8_bin DEFAULT NULL,
  `IsOffer` char(1) COLLATE utf8_bin DEFAULT NULL,
  `OfferId` varchar(35) COLLATE utf8_bin DEFAULT NULL,
  `TermsOfPaymentID` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `TermsOfDeliveryID` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `MinOrderValue` decimal(19,2) DEFAULT NULL,
  `FreeShippingBoundary` decimal(19,2) DEFAULT NULL,
  `FreightSurcharge` decimal(19,2) DEFAULT NULL,
  `SmallVolumeSurcharge` decimal(19,2) DEFAULT NULL,
  `CurrencyID` varchar(3) COLLATE utf8_bin DEFAULT NULL,
  `IsMinOrderValueRequired` char(1) COLLATE utf8_bin DEFAULT NULL,
  `CreatedBy` varchar(60) COLLATE utf8_bin NOT NULL,
  `CreatedOn` datetime NOT NULL,
  `ChangedBy` varchar(60) COLLATE utf8_bin NOT NULL,
  `ChangedOn` datetime NOT NULL,
  `TagID` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `RequireSafetyCheck` char(1) COLLATE utf8_bin DEFAULT 'N',
  `RequireChemicalCheck` char(1) COLLATE utf8_bin DEFAULT 'N',
  `RequireHazardousCheck` char(1) COLLATE utf8_bin DEFAULT 'N',
  `ContainsChemicalItems` varchar(11) COLLATE utf8_bin DEFAULT 'don''t known',
  `ContainsHazardousItems` varchar(11) COLLATE utf8_bin DEFAULT 'don''t known',
  `ContainsSafetyItems` varchar(11) COLLATE utf8_bin DEFAULT 'don''t known',
  `IsInternal` char(1) COLLATE utf8_bin DEFAULT 'N',
  `MaxOrderValue` decimal(19,2) DEFAULT NULL,
  `TotalContractedAmount` decimal(19,2) DEFAULT NULL,
  `IsFrameContract` char(1) COLLATE utf8_bin DEFAULT 'N',
  `ContractCategoryId` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ContractSN`),
  UNIQUE KEY `UK000000000000020` (`ContractID`),
  KEY `FK000000000000070` (`StatusSN`),
  KEY `FK000000000000071` (`ParentContractSN`),
  KEY `FK000000000000072` (`CurrencyID`),
  KEY `FK000000000000073` (`TermsOfPaymentID`),
  KEY `FK000000000000074` (`TermsOfDeliveryID`),
  KEY `FK000000000001177` (`TagID`),
  KEY `FK000000000011310` (`ContractCategoryId`),
  CONSTRAINT `FK000000000000070` FOREIGN KEY (`StatusSN`) REFERENCES `status` (`StatusSN`),
  CONSTRAINT `FK000000000000071` FOREIGN KEY (`ParentContractSN`) REFERENCES `contract` (`ContractSN`),
  CONSTRAINT `FK000000000000072` FOREIGN KEY (`CurrencyID`) REFERENCES `currency` (`CurrencyID`),
  CONSTRAINT `FK000000000000073` FOREIGN KEY (`TermsOfPaymentID`) REFERENCES `termsofpayment` (`TermsOfPaymentID`),
  CONSTRAINT `FK000000000000074` FOREIGN KEY (`TermsOfDeliveryID`) REFERENCES `termsofdelivery` (`TermsOfDeliveryID`),
  CONSTRAINT `FK000000000001177` FOREIGN KEY (`TagID`) REFERENCES `tag` (`TagID`),
  CONSTRAINT `FK000000000011310` FOREIGN KEY (`ContractCategoryId`) REFERENCES `contractcategory` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=53383 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

ContractProvisioning
CREATE TABLE `contractprovisioning` (
  `ContractProvisioningSN` bigint(20) NOT NULL AUTO_INCREMENT,
  `ContractSN` bigint(20) NOT NULL,
  `SupplierSN` bigint(20) NOT NULL,
  `CurrentVersion` int(11) DEFAULT NULL,
  `ReleasedVersion` int(11) DEFAULT NULL,
  `StatusSN` bigint(20) NOT NULL,
  `CreatedBy` varchar(60) COLLATE utf8_bin NOT NULL,
  `CreatedOn` datetime NOT NULL,
  `ChangedBy` varchar(60) COLLATE utf8_bin NOT NULL,
  `ChangedOn` datetime NOT NULL,
  `CurrentValidFrom` datetime DEFAULT NULL,
  `CurrentValidTo` datetime DEFAULT NULL,
  `NewValidFrom` datetime DEFAULT NULL,
  `NewValidTo` datetime DEFAULT NULL,
  `SentMailConfirmation` int(11) DEFAULT NULL,
  PRIMARY KEY (`ContractProvisioningSN`),
  UNIQUE KEY `UK000000000000021` (`ContractSN`,`SupplierSN`),
  KEY `FK000000000000079` (`StatusSN`),
  KEY `FK000000000000081` (`ContractSN`),
  KEY `FK000000000000080` (`SupplierSN`),
  CONSTRAINT `FK000000000000079` FOREIGN KEY (`StatusSN`) REFERENCES `status` (`StatusSN`),
  CONSTRAINT `FK000000000000080` FOREIGN KEY (`SupplierSN`) REFERENCES `supplier` (`SupplierSN`),
  CONSTRAINT `FK000000000000081` FOREIGN KEY (`ContractSN`) REFERENCES `contract` (`ContractSN`)
) ENGINE=InnoDB AUTO_INCREMENT=48976 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

ContractUsage
CREATE TABLE `contractusage` (
  `ContractUsageSN` bigint(20) NOT NULL AUTO_INCREMENT,
  `ContractSN` bigint(20) NOT NULL,
  `CustomerSN` bigint(20) DEFAULT NULL,
  `CreatedBy` varchar(60) COLLATE utf8_bin NOT NULL,
  `CreatedOn` datetime NOT NULL,
  `ChangedBy` varchar(60) COLLATE utf8_bin NOT NULL,
  `ChangedOn` datetime NOT NULL,
  PRIMARY KEY (`ContractUsageSN`),
  UNIQUE KEY `UK000000000000022` (`ContractSN`,`CustomerSN`),
  KEY `FK000000000000082` (`CustomerSN`),
  KEY `FK000000000000083` (`ContractSN`),
  CONSTRAINT `FK000000000000082` FOREIGN KEY (`CustomerSN`) REFERENCES `customer` (`CustomerSN`),
  CONSTRAINT `FK000000000000083` FOREIGN KEY (`ContractSN`) REFERENCES `contract` (`ContractSN`)
) ENGINE=InnoDB AUTO_INCREMENT=47047 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Entitlement
CREATE TABLE `entitlement` (
  `EntitlementSN` bigint(20) NOT NULL AUTO_INCREMENT,
  `RefererType` varchar(40) COLLATE utf8_bin NOT NULL,
  `EntitledContentId` bigint(20) DEFAULT NULL,
  `EntitledContract` bigint(20) DEFAULT NULL,
  `CustomerID` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `SupplierID` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `ClassificationID` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `ClassificationGroupID` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `CatalogID` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `ProductID` varchar(35) COLLATE utf8_bin DEFAULT NULL,
  `CountryID` varchar(2) COLLATE utf8_bin DEFAULT NULL,
  `Company` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `UserID` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `BusinessUnit` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `WorkLocation` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `PurchaseType` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `ProjectCode` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `CostCenter` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `ChangedBy` varchar(60) COLLATE utf8_bin NOT NULL,
  `ChangedOn` datetime NOT NULL,
  `CreatedBy` varchar(60) COLLATE utf8_bin NOT NULL,
  `CreatedOn` datetime NOT NULL,
  `ContractId` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `EntitledHtmlContentUri` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `EntitledClassificationID` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `EntitledClassificationGroupID` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`EntitlementSN`),
  KEY `FK000000000001184` (`EntitledContract`),
  KEY `Entitlement_idx_customer` (`RefererType`,`CustomerID`),
  KEY `Entitlement_idx_product` (`RefererType`,`CatalogID`,`ProductID`),
  KEY `Entitlement_idx_supplier` (`RefererType`,`SupplierID`),
  KEY `Entitlement_idx_classification` (`RefererType`,`ClassificationID`,`ClassificationGroupID`),
  KEY `Entitlement_idx_contract` (`RefererType`,`ContractId`),
  CONSTRAINT `FK000000000001184` FOREIGN KEY (`EntitledContract`) REFERENCES `contract` (`ContractSN`)
) ENGINE=InnoDB AUTO_INCREMENT=435959 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

ProductCatalog
CREATE TABLE `productcatalog` (
  `ProductCatalogSN` bigint(20) NOT NULL AUTO_INCREMENT,
  `ProductCatalogID` varchar(50) COLLATE utf8_bin NOT NULL,
  `ValidFrom` datetime DEFAULT NULL,
  `ValidTo` datetime DEFAULT NULL,
  `StatusSN` bigint(20) NOT NULL,
  `Version` varchar(7) COLLATE utf8_bin DEFAULT NULL,
  `Description` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `Type` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `NumberOfProducts` int(11) DEFAULT NULL,
  `CreatedBy` varchar(60) COLLATE utf8_bin NOT NULL,
  `CreatedOn` datetime NOT NULL,
  `ChangedBy` varchar(60) COLLATE utf8_bin NOT NULL,
  `ChangedOn` datetime NOT NULL,
  `RequireSafetyCheck` char(1) COLLATE utf8_bin DEFAULT 'N',
  `RequireChemicalCheck` char(1) COLLATE utf8_bin DEFAULT 'N',
  `RequireHazardousCheck` char(1) COLLATE utf8_bin DEFAULT 'N',
  `ContainsChemicalItems` varchar(11) COLLATE utf8_bin DEFAULT 'don''t known',
  `ContainsHazardousItems` varchar(11) COLLATE utf8_bin DEFAULT 'don''t known',
  `ContainsSafetyItems` varchar(11) COLLATE utf8_bin DEFAULT 'don''t known',
  `IsPunchoutEditSupported` char(1) COLLATE utf8_bin DEFAULT NULL,
  `IsPunchoutOnly` char(1) COLLATE utf8_bin DEFAULT 'N',
  `CatalogCategoryId` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ProductCatalogSN`),
  UNIQUE KEY `UK000000000000045` (`ProductCatalogID`),
  KEY `FK000000000000175` (`StatusSN`),
  KEY `FK000000000011311` (`CatalogCategoryId`),
  CONSTRAINT `FK000000000000175` FOREIGN KEY (`StatusSN`) REFERENCES `status` (`StatusSN`),
  CONSTRAINT `FK000000000011311` FOREIGN KEY (`CatalogCategoryId`) REFERENCES `catalogcategory` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=49881 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Supplier - 
CREATE TABLE `supplier` (
  `SupplierSN` bigint(20) NOT NULL AUTO_INCREMENT,
  `SupplierID` varchar(30) COLLATE utf8_bin NOT NULL,
  `DUNSNo` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `SupplierGroupSN` bigint(20) DEFAULT NULL,
  `SupplierName` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `StatusSN` bigint(20) DEFAULT NULL,
  `TermsOfPaymentID` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `TermsOfDeliveryID` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `TaxIdentificationNo` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `ExtSupplierID` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `MethodOfPaymentID` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `AccountNumber` varchar(35) COLLATE utf8_bin DEFAULT NULL,
  `BankIdentificationCode` varchar(15) COLLATE utf8_bin DEFAULT NULL,
  `SwiftCode` varchar(11) COLLATE utf8_bin DEFAULT NULL,
  `BankCountryKey` varchar(3) COLLATE utf8_bin DEFAULT NULL,
  `ExtBankControllKey` varchar(2) COLLATE utf8_bin DEFAULT NULL,
  `CreatedBy` varchar(60) COLLATE utf8_bin NOT NULL,
  `CreatedOn` datetime NOT NULL,
  `ChangedBy` varchar(60) COLLATE utf8_bin NOT NULL,
  `ChangedOn` datetime NOT NULL,
  `LanguageID` varchar(4) COLLATE utf8_bin DEFAULT NULL,
  `CurrencyID` varchar(3) COLLATE utf8_bin DEFAULT NULL,
  `ExtAccountCode` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `ExtName` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `ExtShortName` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `ExtGlobalID` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `Logo` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`SupplierSN`),
  UNIQUE KEY `UK000000000000056` (`SupplierID`),
  KEY `FK000000000000270` (`StatusSN`),
  KEY `FK000000000000271` (`CurrencyID`),
  KEY `FK000000000000272` (`TermsOfPaymentID`),
  KEY `FK000000000000273` (`MethodOfPaymentID`),
  KEY `FK000000000000274` (`LanguageID`),
  KEY `FK000000000000275` (`TermsOfDeliveryID`),
  KEY `FK000000000000276` (`SupplierGroupSN`),
  CONSTRAINT `FK000000000000270` FOREIGN KEY (`StatusSN`) REFERENCES `status` (`StatusSN`),
  CONSTRAINT `FK000000000000271` FOREIGN KEY (`CurrencyID`) REFERENCES `currency` (`CurrencyID`),
  CONSTRAINT `FK000000000000272` FOREIGN KEY (`TermsOfPaymentID`) REFERENCES `termsofpayment` (`TermsOfPaymentID`),
  CONSTRAINT `FK000000000000273` FOREIGN KEY (`MethodOfPaymentID`) REFERENCES `methodofpayment` (`MethodOfPaymentID`),
  CONSTRAINT `FK000000000000274` FOREIGN KEY (`LanguageID`) REFERENCES `isolanguage` (`LanguageID`),
  CONSTRAINT `FK000000000000275` FOREIGN KEY (`TermsOfDeliveryID`) REFERENCES `termsofdelivery` (`TermsOfDeliveryID`),
  CONSTRAINT `FK000000000000276` FOREIGN KEY (`SupplierGroupSN`) REFERENCES `suppliergroup` (`SupplierGroupSN`)
) ENGINE=InnoDB AUTO_INCREMENT=19096 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

SupplierProvisioning
CREATE TABLE `supplierprovisioning` (
  `SupplierProvisioningSN` bigint(20) NOT NULL AUTO_INCREMENT,
  `CustomerSN` bigint(20) NOT NULL,
  `SupplierSN` bigint(20) NOT NULL,
  `CurrencyID` varchar(3) COLLATE utf8_bin DEFAULT NULL,
  `CustomerNo` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `EMailForOrder` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `FaxForOrder` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `GeneralOrderText` varchar(500) COLLATE utf8_bin DEFAULT NULL,
  `ManualProdReceipt` char(1) COLLATE utf8_bin DEFAULT NULL,
  `MinOrderValue` int(11) DEFAULT NULL,
  `ValidFrom` datetime DEFAULT NULL,
  `ValidTo` datetime DEFAULT NULL,
  `VendorNo` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `StatusSN` bigint(20) NOT NULL,
  `CustomerSupplierID` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `SupplierCustomerID` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `AccountNo` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `Type` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `CreatedBy` varchar(60) COLLATE utf8_bin NOT NULL,
  `CreatedOn` datetime NOT NULL,
  `ChangedBy` varchar(60) COLLATE utf8_bin NOT NULL,
  `ChangedOn` datetime NOT NULL,
  `MinOrderValueCurrencyID` varchar(3) COLLATE utf8_bin DEFAULT NULL,
  `WaitingPeriod` int(11) DEFAULT NULL,
  `LimitAmountForAutoGoodsReceipt` decimal(19,2) DEFAULT NULL,
  `IsDiversitySupplier` char(1) COLLATE utf8_bin DEFAULT 'N',
  `IsEnvPrefQualifiedSupplier` char(1) COLLATE utf8_bin DEFAULT 'N',
  `IsPreferredSupplier` char(1) COLLATE utf8_bin DEFAULT 'N',
  `MinOrderBehaviour` int(11) DEFAULT NULL,
  `IsForUpload` char(1) COLLATE utf8_bin NOT NULL DEFAULT 'Y',
  `IsDeliveryRated` char(1) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`SupplierProvisioningSN`),
  UNIQUE KEY `UK000000000000060` (`CustomerSN`,`SupplierSN`),
  KEY `FK000000000000286` (`StatusSN`),
  KEY `FK000000000000287` (`SupplierSN`),
  KEY `FK000000000000288` (`CurrencyID`),
  KEY `FK000000000000289` (`CustomerSN`),
  KEY `FK000000000000327` (`MinOrderValueCurrencyID`),
  CONSTRAINT `FK000000000000286` FOREIGN KEY (`StatusSN`) REFERENCES `status` (`StatusSN`),
  CONSTRAINT `FK000000000000287` FOREIGN KEY (`SupplierSN`) REFERENCES `supplier` (`SupplierSN`),
  CONSTRAINT `FK000000000000288` FOREIGN KEY (`CurrencyID`) REFERENCES `currency` (`CurrencyID`),
  CONSTRAINT `FK000000000000289` FOREIGN KEY (`CustomerSN`) REFERENCES `customer` (`CustomerSN`),
  CONSTRAINT `FK000000000000327` FOREIGN KEY (`MinOrderValueCurrencyID`) REFERENCES `currency` (`CurrencyID`)
) ENGINE=InnoDB AUTO_INCREMENT=19776 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Table - Status
Name                 Engine	Ver Row_format	Rows	  Avg_row_len Data_len Max_data_len	Index_len Data_free	Auto_inc	Create_time	         Upd_time	Chk_time	Collation	Checksum	Create_options
catalogprovisioning	InnoDB	10	 Compact	    42079  137	         5783552	   0	      10551296	   4194304	46869	   2015-10-01 05:30:20	NULL	   NULL	utf8_bin	      NULL
catalogusage         InnoDB	10	 Compact	    43728  108	         4734976	   0	       6864896	   4194304	45407	   2015-10-01 05:30:28	NULL	   NULL	utf8_bin	      NULL
contract	            InnoDB	10	 Compact	    44446  248	        11026432	   0	      13762560	   4194304	53383	   2015-10-01 05:30:42	NULL	   NULL	utf8_bin	      NULL
contractprovisioning	InnoDB	10	 Compact	    45052  128	         5783552	   0	      10551296	   4194304	48976	   2015-10-01 05:31:26	NULL	   NULL	utf8_bin	      NULL
contractusage        InnoDB	10	 Compact	    44066  107	         4734976	   0	       6864896	   4194304	47047	   2015-10-01 05:31:31	NULL	   NULL	utf8_bin	      NULL
entitlement	         InnoDB	10	 Compact	   223736  129	        28901376	   0	      39944192	   7340032	435959   2015-10-01 05:31:46	NULL	   NULL	utf8_bin	      NULL
supplier	            InnoDB	10	 Compact	    19163  192	         3686400     0	       4243456	   4194304	19096	   2015-10-01 06:09:20	NULL	   NULL	utf8_bin	      NULL	
supplierprovisioning	InnoDB	10	 Compact	    18253  144	         2637824	   0	       4800512	   4194304	19776	   2015-10-01 06:09:26	NULL	   NULL	utf8_bin	      NULL

MySQL Configuration

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
character-set-server = utf8
collation-server = utf8_bin
default-storage-engine = INNODB

event-scheduler = 1
expire_logs_days=4
general_log=1

innodb_additional_mem_pool_size=40M
innodb_buffer_pool_size = 36G
innodb_buffer_pool_instances = 2
innodb_data_file_path = ibdata1:10M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity=2000
innodb_lock_wait_timeout = 50
innodb_log_buffer_size = 16M
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_read_io_threads=64
innodb_use_native_aio=1
innodb_file_per_table=1
innodb_write_io_threads=64
innodb_thread_concurrency=0
innodb_print_all_deadlocks=1

key_buffer_size = 256M

log_bin=/var/log/mysql/mysql-bin.log
log-output=TABLE

long_query_time = 60
lower-case-table-names = 1

max_allowed_packet = 32M
max_connections = 1600

net_buffer_length = 2K

query_cache_limit = 32M
query_cache_size = 128M
query_cache_type = 1

read_buffer_size = 256K
read_rnd_buffer_size = 256K

slow-query-log = 1
sort_buffer_size = 64K
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
table_open_cache = 175000

tmpdir=/tmp/mysqltmp/
tmp_table_size=2G
max_heap_table_size=2G
thread_cache_size = 8
thread_stack = 256k

wait_timeout=180

server-id = 1

binlog_format=mixed

I am using a 3GB tempfs for RAM Disk

Any thoughts or suggestions?



Edited 1 time(s). Last edit at 10/01/2015 10:47PM by Peter Brawley.

Options: ReplyQuote


Subject
Views
Written By
Posted
Too much time in Copy to Tmp Table
3047
October 01, 2015 11:55AM
1080
October 08, 2015 07:21PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.