Re: Table Not Updating
Hi Peter,
I dont have any indexes on any of my tables.
I am using innodb.
Result of Explain
id Select_type table type rows Extra
1 Simple C ALL 1515
1 Simple S ALL 145195 using Where:Uing Join buffer
innodb_buffer_pool_size=107M
CREATE TABLE `customers` (
`customerID` varchar(7) NOT NULL DEFAULT ' ',
`accountManagerID` varchar(3) NOT NULL DEFAULT ' ',
`address1` varchar(35) NOT NULL DEFAULT ' ',
`accountsEmail` varchar(50) NOT NULL DEFAULT ' ',
`address2` varchar(35) NOT NULL DEFAULT ' ',
`address3` varchar(35) NOT NULL DEFAULT ' ',
`address4` varchar(35) NOT NULL DEFAULT ' ',
`AccountSetUp` date NOT NULL DEFAULT '0000-00-00',
`agentFlag` enum('T','F') NOT NULL DEFAULT 'F',
`areaID` varchar(2) NOT NULL DEFAULT ' ',
`accountsContact` varchar(35) NOT NULL DEFAULT ' ',
`balance` decimal(14,2) NOT NULL DEFAULT '0.00',
`bankID` varchar(10) NOT NULL DEFAULT ' ',
`bankAccount` varchar(20) NOT NULL DEFAULT ' ',
`baseRatePence` decimal(8,2) NOT NULL DEFAULT '0.00',
`baseRateCents` decimal(8,2) NOT NULL DEFAULT '0.00',
`baseRatePC` decimal(5,2) NOT NULL DEFAULT '0.00',
`creditLimit` int(11) NOT NULL DEFAULT '0',
`classification` varchar(1) NOT NULL DEFAULT ' ',
`CCmanagerID` varchar(3) NOT NULL DEFAULT ' ',
`creditreference` varchar(10) NOT NULL DEFAULT ' ',
`creditInfo` varchar(50) NOT NULL DEFAULT ' ',
`creditLimitCurrency` varchar(3) NOT NULL DEFAULT ' ',
`creditFlag` varchar(1) NOT NULL DEFAULT ' ',
`countryID` varchar(2) NOT NULL DEFAULT ' ',
`deliveryAddressFlag` enum('T','F') NOT NULL DEFAULT 'F',
`delTime` varchar(10) NOT NULL DEFAULT ' ',
`deliveryTerms` varchar(3) NOT NULL DEFAULT ' ',
`discountPC` decimal(5,2) NOT NULL DEFAULT '0.00',
`discount2PC` decimal(5,2) NOT NULL DEFAULT '0.00',
`discountPCExtra` decimal(5,2) NOT NULL DEFAULT '0.00',
`DOLO` date NOT NULL DEFAULT '0000-00-00',
`EDI` enum('T','F') NOT NULL DEFAULT 'F',
`euroCreditLimit` decimal(13,2) NOT NULL DEFAULT '0.00',
`expiryDate` date DEFAULT '0000-00-00',
`Excess` decimal(10,2) NOT NULL DEFAULT '0.00',
`fax` varchar(20) NOT NULL DEFAULT ' ',
`frozen` enum('T','F') NOT NULL DEFAULT 'F',
`groupID` varchar(3) NOT NULL DEFAULT ' ',
`invoiceComm` varchar(1) NOT NULL DEFAULT ' ',
`invoiceLang` varchar(2) NOT NULL DEFAULT ' ',
`InvoiceCurrency` varchar(3) NOT NULL DEFAULT ' ',
`invoiceEmail` varchar(250) NOT NULL DEFAULT ' ',
`invoiceEmail2` varchar(250) NOT NULL DEFAULT ' ',
`invoiceText` varchar(100) DEFAULT NULL,
`labelFormat` varchar(1) NOT NULL DEFAULT ' ',
`labelType` int(11) NOT NULL DEFAULT '0',
`number` varchar(4) NOT NULL DEFAULT ' ',
`name` varchar(100) NOT NULL DEFAULT ' ',
`name2` varchar(50) DEFAULT NULL,
`quotationComm` varchar(1) NOT NULL DEFAULT ' ',
`quotationLang` varchar(2) NOT NULL DEFAULT ' ',
`quotationEmail` varchar(254) NOT NULL DEFAULT ' ',
`quotationEmailFrozen` varchar(254) DEFAULT NULL,
`salesContact` varchar(35) NOT NULL DEFAULT ' ',
`salesContactEmail` varchar(50) DEFAULT NULL,
`searchName` varchar(50) NOT NULL DEFAULT ' ',
`shortName` varchar(30) NOT NULL DEFAULT ' ',
`salesTemp` decimal(13,2) NOT NULL DEFAULT '0.00',
`telephone` varchar(20) NOT NULL DEFAULT ' ',
`tradingTermsID` varchar(1) NOT NULL DEFAULT ' ',
`transact_userID` varchar(3) DEFAULT NULL,
`transportUKID` varchar(2) NOT NULL DEFAULT ' ',
`transportEUID1` varchar(3) NOT NULL DEFAULT ' ',
`transportUKIDfri` varchar(2) DEFAULT '',
`transportEUIDfri` varchar(3) DEFAULT '',
`transportEUID2` varchar(3) NOT NULL DEFAULT ' ',
`transportRateEU` decimal(5,2) NOT NULL DEFAULT '0.00',
`transportRateEUExtra` decimal(5,2) NOT NULL DEFAULT '0.00',
`TermsDays` int(11) NOT NULL DEFAULT '0',
`transportPencePerKG` decimal(5,2) NOT NULL DEFAULT '0.00',
`terms` varchar(3) NOT NULL DEFAULT ' ',
`VATreg` varchar(15) NOT NULL DEFAULT '',
`pkey` int(11) NOT NULL AUTO_INCREMENT,
`warehouseID` varchar(1) DEFAULT '',
`inUse` varchar(25) DEFAULT ' ',
`lastUsedBy` varchar(25) DEFAULT ' ',
`town` varchar(45) DEFAULT ' ',
`postcode` varchar(12) DEFAULT ' ',
`blocked` enum('T','F') DEFAULT 'F',
`customerRefFlag` enum('T','F') DEFAULT 'F',
`insuranceExcluded` enum('T','F') DEFAULT 'F',
`printBonDeLivraison` enum('T','F') DEFAULT 'T',
`printBonDeTransport` enum('T','F') DEFAULT 'T',
`lastUpdated` date DEFAULT NULL,
`kebabBestBeforeDays` int(11) DEFAULT '0',
`smokedBestBeforeDays` int(11) DEFAULT '0',
`termsDaysFrozen` int(11) DEFAULT '0',
`agentID` varchar(8) DEFAULT 'NON',
`quotationFaxNo` varchar(50) DEFAULT NULL,
`quotation_Mon` enum('T','F') NOT NULL DEFAULT 'T',
`quotation_Tue` enum('T','F') NOT NULL DEFAULT 'T',
`quotation_Wed` enum('T','F') NOT NULL DEFAULT 'T',
`quotation_Thu` enum('T','F') NOT NULL DEFAULT 'T',
`quotation_Fri` enum('T','F') NOT NULL DEFAULT 'T',
`copyOver` enum('T','F') NOT NULL DEFAULT 'F',
`bar01` enum('T','F') DEFAULT 'T',
`bar3103` enum('T','F') DEFAULT 'F',
`bar10` enum('T','F') DEFAULT 'F',
`bar15` enum('T','F') DEFAULT 'F',
`frozenQuotationCode` varchar(1) DEFAULT NULL,
`GLN` varchar(20) DEFAULT NULL,
`EDIEmail` varchar(50) DEFAULT NULL,
`auxID` varchar(25) NOT NULL DEFAULT '',
`quotationEUFreshCode` varchar(3) NOT NULL DEFAULT '',
`printEUFresh` varchar(1) NOT NULL DEFAULT 'F',
`quotationEmailText` varchar(254) DEFAULT NULL,
`quotationEmailText2` varchar(254) DEFAULT NULL,
`quotationEmailText3` varchar(254) DEFAULT NULL,
`agentEmail` varchar(254) DEFAULT NULL,
`printHardCopy` enum('T','F') DEFAULT 'F',
`printSpainFresh` varchar(1) NOT NULL DEFAULT '',
`IC_baseRatePence` decimal(11,2) NOT NULL DEFAULT '0.00',
`IC_baseRatePC` decimal(11,2) NOT NULL DEFAULT '0.00',
`hasOrderNum` enum('T','F') NOT NULL DEFAULT 'F' COMMENT 'customers has internal order number (NOT cust reference)',
`showBestBeforeOnInvoice` enum('T','F') DEFAULT 'F',
`invoiceFaxNo` varchar(20) DEFAULT NULL,
`invoiceFaxNo2` varchar(20) DEFAULT '',
`hasOverdueBalance` enum('T','F') DEFAULT 'F',
PRIMARY KEY (`pkey`)
) ENGINE=InnoDB AUTO_INCREMENT=573261 DEFAULT CHARSET=latin1
CREATE TABLE `salesledger` (
`accountManagerID` varchar(3) DEFAULT ' ',
`AOLP` decimal(14,2) NOT NULL DEFAULT '0.00',
`balance` decimal(14,2) NOT NULL DEFAULT '0.00',
`bankCharges` decimal(12,2) NOT NULL DEFAULT '0.00',
`cash` decimal(14,2) DEFAULT '0.00',
`CCmanagerID` varchar(3) DEFAULT ' ',
`conversionRate` decimal(10,3) NOT NULL DEFAULT '0.000',
`costcentre` varchar(1) DEFAULT ' ',
`countryID` varchar(2) NOT NULL DEFAULT ' ',
`creditDaysTaken` int(11) NOT NULL DEFAULT '0',
`customerID` varchar(7) NOT NULL DEFAULT ' ',
`dateExported` date DEFAULT '0000-00-00',
`daybook` enum('T','F') NOT NULL DEFAULT 'F',
`docType` varchar(3) DEFAULT ' ',
`discount` decimal(12,2) NOT NULL DEFAULT '0.00',
`DOLP` date NOT NULL DEFAULT '0000-00-00',
`dueDate` date DEFAULT '0000-00-00',
`exportedToSage` enum('T','F') DEFAULT 'F',
`groupID` varchar(3) DEFAULT ' ',
`invNum` int(11) NOT NULL DEFAULT '0',
`invoiceCurrency` varchar(3) NOT NULL DEFAULT ' ',
`invoiceDate` date NOT NULL DEFAULT '0000-00-00',
`net` decimal(14,2) NOT NULL DEFAULT '0.00',
`netWeight` decimal(14,3) DEFAULT NULL,
`orderref` int(11) NOT NULL DEFAULT '0',
`payable` decimal(14,2) NOT NULL DEFAULT '0.00',
`paymentStatus` varchar(1) NOT NULL DEFAULT ' ',
`paidToDate` decimal(14,2) NOT NULL DEFAULT '0.00',
`PDF` varchar(100) DEFAULT NULL,
`pkey` int(11) NOT NULL AUTO_INCREMENT,
`receiptNo` int(11) DEFAULT '0',
`taxCode` varchar(2) DEFAULT ' ',
`transactionType` varchar(2) NOT NULL DEFAULT ' ',
`userID` varchar(3) DEFAULT ' ',
`vat` decimal(13,2) NOT NULL DEFAULT '0.00',
`lastUpdated` date DEFAULT NULL,
`boxes` int(11) DEFAULT '0',
`freshFrozen` varchar(1) DEFAULT 'F',
`monthPeriodEndFlag` enum('F','T') DEFAULT 'F',
`comment` varchar(250) DEFAULT NULL,
`invoiceLogFlag` enum('F','T') DEFAULT 'F',
`agentID` varchar(3) DEFAULT 'NON',
`accountID` varchar(10) DEFAULT NULL,
`daybookNo` int(11) DEFAULT '0',
`paymentNo` int(12) NOT NULL DEFAULT '0',
`balAccType` varchar(10) DEFAULT NULL,
`ediSent` date DEFAULT NULL,
`ediFlag` enum('T','F') DEFAULT 'F',
`dayBookNo2` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`pkey`)
) ENGINE=InnoDB AUTO_INCREMENT=2955772 DEFAULT CHARSET=latin1
Hope this helps.