MySQL Forums
Forum List  »  Stored Procedures

Re: Query times out when I add extra Where Condition
Posted by: John Noble
Date: January 28, 2020 05:30AM

Hi Peter,

If I change my innodb_buffer_pool_size to anything above 2000M I cant get mysql to restart. SO I have kept it at 2000M.

Im not sure how to tell how much of the 8G RAM is available to mysql ??

I ran your query using the 5 tables in question....
select engine,data,indexes,total
from (
  select 
    ifnull(engine,'TOTALS') as engine, 
    concat(data,' GB') as data, 
    concat(indexes,' GB') as indexes, 
    concat(tot,' GB') as total,
    if(engine is null,-1,tot) as ord
  from (
    select   
      engine,  
      round( sum(data_length)/1024/1024/1024, 2 ) as data,  
      round( sum(index_length)/1024/1024/1024, 2 ) as indexes,  
      round( sum(data_length+index_length)/1024/1024/1024, 2 ) as tot
    from information_schema.tables
    where table_schema= "sst" and table_name in("salesledger", "receipts", "users", "countries", "groups")
    group by engine with rollup
  ) sums
) list
order by list.ord desc;

'InnoDB', '0.12 GB', '0.05 GB', '0.17 GB'
'TOTALS', '0.12 GB', '0.05 GB', '0.17 GB'

Here are the Create Tables from mySQl CLient...

  CREATE TABLE `salesledger` (
  `accountManagerID` varchar(3) NOT NULL 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) NOT NULL DEFAULT '0.00',
  `CCmanagerID` varchar(3) NOT NULL DEFAULT '',
  `conversionRate` decimal(10,3) NOT NULL DEFAULT '0.000',
  `costcentre` varchar(1) NOT NULL DEFAULT '',
  `countryID` varchar(2) NOT NULL DEFAULT ' ',
  `creditDaysTaken` int(11) NOT NULL DEFAULT '0',
  `customerID` varchar(7) NOT NULL DEFAULT ' ',
  `dateExported` date NOT NULL DEFAULT '0000-00-00',
  `daybook` enum('T','F') NOT NULL DEFAULT 'F',
  `docType` varchar(3) NOT NULL DEFAULT '',
  `discount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `DOLP` date NOT NULL DEFAULT '0000-00-00',
  `dueDate` date NOT NULL DEFAULT '0000-00-00',
  `exportedToSage` enum('T','F') NOT NULL DEFAULT 'F',
  `groupID` varchar(3) NOT NULL 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) NOT NULL DEFAULT '0.000',
  `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) NOT NULL DEFAULT '',
  `pkey` int(11) NOT NULL AUTO_INCREMENT,
  `receiptNo` int(11) NOT NULL DEFAULT '0',
  `taxCode` varchar(2) NOT NULL DEFAULT '',
  `transactionType` varchar(2) NOT NULL DEFAULT ' ',
  `userID` varchar(3) NOT NULL DEFAULT '',
  `vat` decimal(13,2) NOT NULL DEFAULT '0.00',
  `lastUpdated` date NOT NULL DEFAULT '0001-01-01',
  `boxes` int(11) NOT NULL DEFAULT '0',
  `freshFrozen` varchar(1) NOT NULL DEFAULT 'F',
  `monthPeriodEndFlag` enum('F','T') NOT NULL DEFAULT 'F',
  `comment` varchar(250) NOT NULL DEFAULT '',
  `invoiceLogFlag` enum('F','T') NOT NULL DEFAULT 'F',
  `agentID` varchar(3) NOT NULL DEFAULT 'NON',
  `accountID` varchar(10) NOT NULL DEFAULT '',
  `daybookNo` int(11) NOT NULL DEFAULT '0',
  `paymentNo` int(12) NOT NULL DEFAULT '0',
  `balAccType` varchar(10) NOT NULL DEFAULT '',
  `ediSent` date NOT NULL DEFAULT '0001-01-01',
  `ediFlag` enum('T','F') NOT NULL DEFAULT 'F',
  `dayBookNo2` int(11) NOT NULL DEFAULT '0',
  `paidInFullDate` date NOT NULL DEFAULT '0001-01-01',
  `businessGroup` varchar(4) NOT NULL DEFAULT '',
  `vatCode` varchar(10) NOT NULL DEFAULT '',
  `nomCode` varchar(4) NOT NULL DEFAULT '',
  `vatReportFlag` enum('T','F') NOT NULL DEFAULT 'F',
  `vatReportDate` date NOT NULL DEFAULT '0001-01-01',
  `bankAmount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `entryDate` date NOT NULL DEFAULT '0001-01-01',
  `unallocatedNo` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`pkey`),
  KEY `countryID` (`countryID`) USING BTREE,
  KEY `invNum` (`invNum`) USING BTREE,
  KEY `customerID` (`customerID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3207448 DEFAULT CHARSET=latin1



   CREATE TABLE `users` (
  `username` varchar(15) NOT NULL DEFAULT ' ',
  `password` varchar(15) NOT NULL DEFAULT ' ',
  `Name` varchar(30) NOT NULL DEFAULT ' ',
  `countryID` varchar(2) NOT NULL DEFAULT ' ',
  `entries` int(11) NOT NULL DEFAULT '0',
  `userID` varchar(3) NOT NULL DEFAULT ' ',
  `Gender` varchar(1) NOT NULL DEFAULT ' ',
  `loggedIn` enum('T','F') NOT NULL DEFAULT 'F',
  `accountManager` enum('T','F') NOT NULL DEFAULT 'F',
  `ccmanager` enum('T','F') NOT NULL DEFAULT 'F',
  `labelPrinterName` varchar(50) NOT NULL DEFAULT ' ',
  `pkey` int(11) NOT NULL AUTO_INCREMENT,
  `overrideFileMaintenance` enum('T','F') DEFAULT 'F',
  `presentationNames` enum('T','F') DEFAULT 'T',
  `gradeNames` enum('T','F') DEFAULT 'F',
  `customerNames` enum('T','F') DEFAULT 'F',
  `modifyOrder` enum('T','F') DEFAULT 'F',
  `lastCustomerDownload` date DEFAULT NULL,
  `emailAddress` varchar(45) DEFAULT ' ',
  `emailpassword` varchar(45) DEFAULT ' ',
  `outgoingSMTP` varchar(100) DEFAULT ' ',
  `emailPort` int(11) DEFAULT '25',
  `lastSalesLedgerDownload` date DEFAULT NULL,
  `lastBankDownload` date DEFAULT NULL,
  `copyLocalFiles` enum('T','F') DEFAULT 'T',
  `clearProductLine` enum('T','F') DEFAULT 'F',
  `customerBoardNo` int(11) DEFAULT '0',
  `currentCustomerBoard` varchar(30) DEFAULT NULL,
  `PL` enum('T','F') DEFAULT 'F',
  `UKSales` enum('T','F') NOT NULL DEFAULT 'F',
  `location` varchar(2) NOT NULL DEFAULT '',
  `generalPostingFromDate` date NOT NULL DEFAULT '0001-01-01
  `generalPostingToDate` date NOT NULL DEFAULT '0001-01-01',
  PRIMARY KEY (`pkey`)
) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=latin1  


   CREATE TABLE `countries` (
  `countryID` varchar(2) NOT NULL DEFAULT ' ',
  `country` varchar(20) NOT NULL DEFAULT ' ',
  `czechName` varchar(20) NOT NULL DEFAULT ' ',
  `EUMember` enum('T','F') NOT NULL DEFAULT 'F',
  `EUTaxCode` varchar(2) NOT NULL DEFAULT ' ',
  `FrenchName` varchar(20) NOT NULL DEFAULT ' ',
  `GermanName` varchar(20) NOT NULL DEFAULT ' ',
  `italianName` varchar(20) NOT NULL DEFAULT ' ',
  `localTaxCode` varchar(2) NOT NULL DEFAULT ' ',
  `taxCode` varchar(1) NOT NULL DEFAULT ' ',
  `freeFormatVatTaxCode` varchar(2) NOT NULL DEFAULT '',
  `name` varchar(30) NOT NULL DEFAULT ' ',
  `pkey` int(11) NOT NULL AUTO_INCREMENT,
  `productSource` enum('T','F') NOT NULL DEFAULT 'F',
  `SlovakianName` varchar(20) NOT NULL DEFAULT ' ',
  `SpanishName` varchar(20) NOT NULL DEFAULT ' ',
  `TransportTaxCode` varchar(2) NOT NULL DEFAULT ' ',
  `lastUsedBy` varchar(45) DEFAULT ' ',
  `inUse` varchar(45) DEFAULT ' ',
  `customerBoardNo` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`pkey`)
) ENGINE=InnoDB AUTO_INCREMENT=135 DEFAULT CHARSET=latin1


       Table: customers
Create Table: CREATE TABLE `customers` (
  `customerID` varchar(7) NOT NULL DEFAULT ' ',
  `accountManagerID` varchar(3) NOT NULL DEFAULT ' ',
  `address1` varchar(35) NOT NULL DEFAULT ' ',
  `accountsEmail` varchar(150) 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 NOT NULL 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(250) NOT NULL DEFAULT '',
  `invoiceText2` varchar(250) NOT NULL DEFAULT '',
  `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) NOT NULL DEFAULT '',
  `quotationComm` varchar(1) NOT NULL DEFAULT ' ',
  `quotationLang` varchar(2) NOT NULL DEFAULT ' ',
  `quotationEmail` varchar(254) NOT NULL DEFAULT ' ',
  `quotationEmailFrozen` varchar(254) NOT NULL DEFAULT '',
  `salesContact` varchar(35) NOT NULL DEFAULT ' ',
  `salesContactEmail` varchar(150) NOT NULL DEFAULT '',
  `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) NOT NULL DEFAULT '',
  `transportUKID` varchar(2) NOT NULL DEFAULT ' ',
  `transportEUID1` varchar(3) NOT NULL DEFAULT ' ',
  `transportUKIDfri` varchar(2) NOT NULL DEFAULT '',
  `transportEUIDfri` varchar(3) NOT NULL 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) NOT NULL DEFAULT '',
  `inUse` varchar(25) NOT NULL DEFAULT ' ',
  `lastUsedBy` varchar(25) NOT NULL DEFAULT ' ',
  `town` varchar(45) NOT NULL DEFAULT ' ',
  `postcode` varchar(12) NOT NULL DEFAULT ' ',
  `blocked` enum('T','F') NOT NULL DEFAULT 'F',
  `customerRefFlag` enum('T','F') NOT NULL DEFAULT 'F',
  `insuranceExcluded` enum('T','F') NOT NULL DEFAULT 'F',
  `printBonDeLivraison` enum('T','F') NOT NULL DEFAULT 'T',
  `printBonDeTransport` enum('T','F') NOT NULL DEFAULT 'T',
  `lastUpdated` date NOT NULL DEFAULT '0001-01-01',
  `kebabBestBeforeDays` int(11) NOT NULL DEFAULT '0',
  `smokedBestBeforeDays` int(11) NOT NULL DEFAULT '0',
  `termsDaysFrozen` int(11) NOT NULL DEFAULT '0',
  `agentID` varchar(8) NOT NULL DEFAULT 'NON',
  `quotationFaxNo` varchar(50) NOT NULL DEFAULT '',
  `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') NOT NULL DEFAULT 'T',
  `bar3103` enum('T','F') NOT NULL DEFAULT 'F',
  `bar10` enum('T','F') NOT NULL DEFAULT 'F',
  `bar15` enum('T','F') NOT NULL DEFAULT 'F',
  `frozenQuotationCode` varchar(1) NOT NULL DEFAULT '',
  `GLN` varchar(20) NOT NULL DEFAULT '',
  `EDIEmail` varchar(50) NOT NULL DEFAULT '',
  `auxID` varchar(25) NOT NULL DEFAULT '',
  `quotationEUFreshCode` varchar(3) NOT NULL DEFAULT '',
  `printEUFresh` varchar(1) NOT NULL DEFAULT 'F',
  `quotationEmailText` varchar(254) NOT NULL DEFAULT '',
  `quotationEmailText2` varchar(254) NOT NULL DEFAULT '',
  `quotationEmailText3` varchar(254) NOT NULL DEFAULT '',
  `agentEmail` varchar(254) NOT NULL DEFAULT '',
  `printHardCopy` enum('T','F') NOT NULL 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 'c
al order number (NOT cust reference)',
  `showBestBeforeOnInvoice` enum('T','F') NOT NULL DEFAULT 'F
  `invoiceFaxNo` varchar(20) NOT NULL DEFAULT '',
  `invoiceFaxNo2` varchar(20) NOT NULL DEFAULT '',
  `quotationNewlynEmail` varchar(250) NOT NULL DEFAULT '',
  `newlynBaseRatePence` decimal(11,2) NOT NULL DEFAULT '0.00'
  `newlynBaseRatePC` decimal(11,2) NOT NULL DEFAULT '0.00',
  `newlynBaseRateCents` decimal(11,2) NOT NULL DEFAULT '0.00'
  `quotationNewlynFreshCode` varchar(3) NOT NULL DEFAULT '',
  `printNewlynFresh` varchar(1) NOT NULL DEFAULT '',
  `splitPDFInvoice` enum('T','F') NOT NULL DEFAULT 'F',
  `transportCompany` varchar(30) NOT NULL DEFAULT '',
  `transportCompanyCode` varchar(20) NOT NULL DEFAULT '',
  `hasOverDueBalance` enum('T','F') NOT NULL DEFAULT 'F',
  `lastStatementDate` date NOT NULL DEFAULT '0000-00-00',
  `GLNEDIIV` varchar(20) NOT NULL DEFAULT '',
  `GLNEDIPR` varchar(20) NOT NULL DEFAULT '',
  `statementEmailAddress` varchar(250) NOT NULL DEFAULT '',
  `bestBeforeDateFormat` varchar(10) NOT NULL DEFAULT '',
  `quotationIrishFreshCode` varchar(4) NOT NULL DEFAULT '',
  `isInternal` enum('T','F') NOT NULL DEFAULT 'F',
  `isCustomerRef` enum('T','F') NOT NULL DEFAULT 'F',
  `excludeFromMailings` enum('T','F') NOT NULL DEFAULT 'F',
  PRIMARY KEY (`pkey`),
  KEY `customerID` (`customerID`) USING BTREE,
  KEY `countryID` (`countryID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=573721 DEFAULT CHARSET=latin1   
  
  CREATE TABLE `groups` (
  `ID` varchar(3) NOT NULL DEFAULT ' ',
  `name` varchar(30) NOT NULL DEFAULT ' ',
  `invInstructions` varchar(100) NOT NULL DEFAULT ' ',
  `inUse` varchar(25) DEFAULT ' ',
  `lastUsedBy` varchar(25) DEFAULT ' ',
  `pkey` int(11) NOT NULL AUTO_INCREMENT,
  `creditReference` varchar(10) DEFAULT NULL,
  `creditLimit` decimal(12,2) DEFAULT '0.00',
  PRIMARY KEY (`pkey`)
) ENGINE=InnoDB AUTO_INCREMENT=1986 DEFAULT CHARSET=latin1


The customers table does has many fields and could be redesigned but it is something that I have inherited. Changing it just is out of the question. Too many other programs rely on it the way it is Im afraid.


Thanks again. Much appreciated.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Query times out when I add extra Where Condition
137
January 28, 2020 05:30AM


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.