Re: 1 row Update query long for short duration

[karl lauritzen]

1. No change to table. Good idea we had thought of that too. Somehting updates this tabel every minute for each user. Each user has their own database. Not a mutli tenant database.

2. This update runs on average about .4ms all day except then. and not everyday it does this behavior. Like this week only on monday it did it and query cache went to 0. Last 2 days no issues. We have MS Access some still so we are limited to 32 indexes per table. show is below on table.

Table,"Create Table"
mastertable,"CREATE TABLE `mastertable` (
`FranchiseID` int(11) NOT NULL,
`AdPercentage` int(11) DEFAULT NULL,
`Agreement` text,
`AgreementAgreedTo` tinyint(1) NOT NULL DEFAULT '0',
`AgreementDate` datetime DEFAULT NULL,
`AllowAutoUpdates` tinyint(1) NOT NULL DEFAULT '1',
`BackupDefaultPath` varchar(255) DEFAULT NULL,
`CalendarShowType` int(11) DEFAULT '0',
`ColorSchApptListEven` int(11) DEFAULT '0',
`ColorSchApptListItem` int(11) DEFAULT '0',
`ColorSchApptListOdd` int(11) DEFAULT '0',
`ColorSchApptListWeekEnd` int(11) DEFAULT '0',
`CompanyName` varchar(50) DEFAULT NULL,
`DefaultWorkOrderTime` int(11) DEFAULT NULL,
`DemoPassword` varchar(8) DEFAULT NULL,
`GSTPercentage` int(11) DEFAULT NULL,
`InvoiceMasterNumber` int(11) DEFAULT NULL,
`InvoiceType` int(11) DEFAULT NULL,
`KillDate` datetime DEFAULT NULL,
`KillMessage` text,
`LastDateSentHome` datetime DEFAULT NULL,
`LastDateSentToQB` datetime DEFAULT NULL,
`LastInvoiceDate` datetime DEFAULT NULL,
`MasterPassword` varchar(8) DEFAULT NULL,
`NEOKillDate` datetime DEFAULT NULL,
`NEOKillMessage` text,
`Notes` text,
`PasswordHR` varchar(50) DEFAULT NULL,
`PasswordTimeClock` varchar(50) DEFAULT NULL,
`POMasterNumber` int(11) DEFAULT NULL,
`POType` int(11) DEFAULT NULL,
`QBPathName` varchar(255) DEFAULT NULL,
`RoyaltyPercentage` int(11) DEFAULT NULL,
`RoyaltyTaxRate` double DEFAULT '0',
`SentHomeMessage` text,
`SetupPassword` varchar(8) DEFAULT NULL,
`StartDate` datetime DEFAULT NULL,
`SystemId` int(11) NOT NULL,
`TaxMessage` text,
`UsePasswords` tinyint(1) NOT NULL DEFAULT '0',
`WarningMessage` text,
`StartSearchType` int(11) DEFAULT '4',
`BeginARBalance` double DEFAULT '0',
`BeginInventory` double DEFAULT '0',
`NotesAboutMySystem` text,
`MasterPasswordSet` tinyint(1) NOT NULL DEFAULT '0',
`QBTransferID` int(11) DEFAULT '1',
`TagLine` varchar(30) DEFAULT NULL,
`License` varchar(50) DEFAULT NULL,
`LicNum` int(11) DEFAULT '0',
`UsePaging` tinyint(1) NOT NULL DEFAULT '1',
`OvertimeHours` int(11) DEFAULT '40',
`OvertimePerDayYN` tinyint(1) NOT NULL DEFAULT '0',
`CaptionMsg` varchar(25) DEFAULT NULL,
`PayrollOvertimeType` int(11) DEFAULT '1',
`PayrollTotalHours` int(11) DEFAULT '1',
`CountryId` int(11) DEFAULT NULL,
`ThirdPartyByAccountOnlyYN` tinyint(1) NOT NULL DEFAULT '0',
`PopupReminderMinutes` int(11) DEFAULT '0',
`LastBackupDate` datetime DEFAULT NULL,
`BackupReminderYN` tinyint(1) NOT NULL DEFAULT '0',
`InventoryType` int(11) DEFAULT NULL,
`InventoryTypeReorderLevel` int(11) DEFAULT NULL,
`UseMPGYN` tinyint(1) NOT NULL DEFAULT '0',
`ValidateTerritory` tinyint(1) NOT NULL DEFAULT '0',
`ColorSchApptListSecondary` int(11) DEFAULT '16777215',
`GroupUnassignedItemsYN` tinyint(1) NOT NULL DEFAULT '1',
`QBDiscountAccount` varchar(75) DEFAULT NULL,
`LastTableUpdatesID` int(11) DEFAULT NULL,
`IntranetURL` varchar(75) DEFAULT NULL,
`GSTNumber` varchar(11) DEFAULT NULL,
`OverheadRate` double DEFAULT '0',
`FTPAddress` varchar(25) DEFAULT NULL,
`FTPUserName` varchar(25) DEFAULT NULL,
`FTPPassword` varchar(25) DEFAULT NULL,
`SOAPAddress` varchar(25) DEFAULT NULL,
`BackupAppendDateYN` tinyint(1) NOT NULL DEFAULT '1',
`BackupQBYN` tinyint(1) NOT NULL DEFAULT '1',
`ProjectType` varchar(50) DEFAULT NULL,
`PayrollPrintTimeYN` tinyint(1) NOT NULL DEFAULT '0',
`QBLockedYN` tinyint(1) NOT NULL DEFAULT '0',
`NAGSLicNum` int(11) DEFAULT '0',
`TrainingDate` datetime DEFAULT '0000-00-00 00:00:00',
`CompanyStoreID` varchar(4) DEFAULT NULL,
`CompanyStoreName` varchar(50) DEFAULT NULL,
`LastLoginDate` datetime DEFAULT NULL,
`SpecialityCodesYN` tinyint(1) NOT NULL DEFAULT '1',
`TerminatedDate` datetime DEFAULT NULL,
`LastLoginName` varchar(50) DEFAULT NULL,
`VisitNumberOfDays` int(11) DEFAULT '0',
`PasswordSystemSetup` varchar(50) DEFAULT NULL,
`RequireCancelReasonYN` tinyint(1) NOT NULL DEFAULT '0',
`TaxIDDefault` int(11) DEFAULT '0',
`DoubleBookingID` int(11) DEFAULT '3',
`NAGSPrintInsInfoYN` tinyint(1) NOT NULL DEFAULT '0',
`PasswordBillTo` varchar(50) DEFAULT NULL,
`ShowLOFPPGYN` tinyint(1) NOT NULL DEFAULT '0',
`SquareFootDefaultCom` double DEFAULT '0',
`SquareFootDefaultRes` double DEFAULT '0',
`RegisterDate` datetime DEFAULT NULL,
`ProductEnabledYN` tinyint(1) NOT NULL DEFAULT '1',
`ServerTUID` varchar(50) DEFAULT NULL,
`ServerMACAddress` varchar(50) DEFAULT NULL,
`ServerPCName` varchar(50) DEFAULT NULL,
`MBOYN` tinyint(1) NOT NULL DEFAULT '0',
`TotalRegions` int(11) DEFAULT '1',
`CashRegisterYN` tinyint(1) NOT NULL DEFAULT '0',
`ComRateType` int(11) DEFAULT '1',
`PrintCreditCardInfoYN` tinyint(1) NOT NULL DEFAULT '1',
`CheckForDuplicateCustomerYN` tinyint(1) NOT NULL DEFAULT '1',
`MaxNumberOfNewOrders` int(11) DEFAULT '3',
`InvoiceNumberLockYN` tinyint(1) NOT NULL DEFAULT '0',
`StopOnNESErrorsYN` tinyint(1) NOT NULL DEFAULT '0',
`CheckPopupMessagesYN` tinyint(1) NOT NULL DEFAULT '0',
`NewPartYN` tinyint(1) NOT NULL DEFAULT '1',
`PrintInvoiceLockFlagYN` tinyint(1) NOT NULL DEFAULT '0',
`NeedAreaYN` tinyint(1) NOT NULL DEFAULT '0',
`QBClosedDate` datetime DEFAULT NULL,
`LockCustomerOnWorkOrderYN` tinyint(1) NOT NULL DEFAULT '1',
`QBTransferYN` tinyint(1) NOT NULL DEFAULT '1',
`UseZipSortYN` tinyint(1) NOT NULL DEFAULT '1',
`WORequireAreaYN` tinyint(1) NOT NULL DEFAULT '1',
`HideCostYN` tinyint(1) NOT NULL DEFAULT '1',
`RequireTaxAuthorityYN` tinyint(1) NOT NULL DEFAULT '1',
`FilterTechByAreaYN` tinyint(1) NOT NULL DEFAULT '0',
`InstallationTypeID` int(11) DEFAULT '1',
`RequireBankAccountYN` tinyint(1) NOT NULL DEFAULT '0',
`RequireJobPromiseDateYN` tinyint(1) NOT NULL DEFAULT '0',
`AllowEditCODYN` tinyint(1) NOT NULL DEFAULT '1',
`DispatchOpenToID` int(11) DEFAULT '1',
`CommTypeLabel1` varchar(15) DEFAULT NULL,
`CommTypeLabel2` varchar(15) DEFAULT NULL,
`CommTypeLabel3` varchar(15) DEFAULT NULL,
`InvoiceAutoTotalYN` tinyint(1) NOT NULL DEFAULT '0',
`InvoiceAutoRefreshYN` tinyint(1) NOT NULL DEFAULT '0',
`StatementOrderHeaderID` int(11) DEFAULT NULL,
`RequireQBClassCodeYN` tinyint(1) NOT NULL DEFAULT '0',
`DispatchRefreshRate` int(11) DEFAULT '0',
`CanRunMultipleInstancesYN` tinyint(1) NOT NULL DEFAULT '0',
`WSAErrorHRID` int(11) DEFAULT '0',
`UseTimeCardYN` tinyint(1) NOT NULL DEFAULT '0',
`DefaultThankYouLetter` varchar(255) DEFAULT NULL,
`MainMenuDefaultViewYN` tinyint(1) NOT NULL DEFAULT '0',
`AutoMarkAddOnTaskYN` tinyint(1) NOT NULL DEFAULT '0',
`YahooMapsYN` tinyint(1) NOT NULL DEFAULT '1',
`SearchDefaultWhere` int(11) DEFAULT '1',
`HomeLifeFTPAddress` varchar(255) DEFAULT NULL,
`HomeLifeFTPUserName` varchar(25) DEFAULT NULL,
`HomeLifeFTPPassword` varchar(25) DEFAULT NULL,
`ProTradeNetFTPAddress` varchar(255) DEFAULT NULL,
`ProTradeNetFTPUserName` varchar(25) DEFAULT NULL,
`ProTradeNetFTPPassword` varchar(25) DEFAULT NULL,
`PivotalNumber` varchar(25) DEFAULT NULL,
`LaborCostPerHour` double DEFAULT '20',
`TuneUpAmount` double DEFAULT '79.95',
`ActualLaborYN` tinyint(1) NOT NULL DEFAULT '0',
`ActualRevenueYN` tinyint(1) NOT NULL DEFAULT '0',
`ProTradeNetDefaultVendorID` int(11) DEFAULT '0',
`ProTradeNetPIN` varchar(15) DEFAULT NULL,
`ProTradeNetBuyerID` varchar(25) DEFAULT NULL,
`DefaultToCustomerAdvancedSearchYN` tinyint(1) NOT NULL DEFAULT '1',
`DefaultToWorkOrderAdvancedSearchYN` tinyint(1) NOT NULL DEFAULT '1',
`WSAUFOCEdition` int(11) DEFAULT '1',
`BatchProcessingYN` tinyint(1) NOT NULL DEFAULT '0',
`NESNumber` varchar(10) DEFAULT NULL,
`PullPartFromLocationID` int(11) DEFAULT '0',
`UseNewDispatchYN` tinyint(1) NOT NULL DEFAULT '0',
`SendWSAFTPYN` tinyint(1) NOT NULL DEFAULT '0',
`ZMobileEnabledYN` tinyint(1) NOT NULL DEFAULT '0',
`UseNewQBYN` tinyint(1) NOT NULL DEFAULT '0',
`PrintLogoOnStatementsYN` tinyint(1) NOT NULL DEFAULT '0',
`RequireCancelCommentYN` tinyint(1) NOT NULL DEFAULT '0',
`DisplayWaitingOnCalendarYN` tinyint(1) NOT NULL DEFAULT '1',
`CommInsteadYN` tinyint(1) NOT NULL DEFAULT '0',
`TaskTaxableYN` tinyint(1) NOT NULL DEFAULT '1',
`ContractType` int(11) DEFAULT '1',
`ContractMasterNumber` int(11) DEFAULT '0',
`DoubleClickPayOptionYN` tinyint(1) NOT NULL DEFAULT '1',
`MRAEDI_FTPAddress` varchar(255) DEFAULT NULL,
`MRAEDI_FTPUserName` varchar(255) DEFAULT NULL,
`MRAEDI_FTPPassword` varchar(255) DEFAULT NULL,
`LastAutomaticDownloadDate` datetime DEFAULT NULL,
`MRAEDI_SrvBench_FTPAddress` varchar(255) DEFAULT NULL,
`MRAEDI_SrvBench_FTPUserName` varchar(255) DEFAULT NULL,
`MRAEDI_SrvBench_FTPPassword` varchar(255) DEFAULT NULL,
`UseUserWorkTimesYN` tinyint(1) NOT NULL DEFAULT '0',
`ZeePhoneNumber` varchar(10) DEFAULT NULL,
`ZeeReplyEMail` varchar(255) DEFAULT NULL,
`SendApptCallsYN` tinyint(1) NOT NULL DEFAULT '0',
`GatewayDisabledYN` tinyint(1) NOT NULL DEFAULT '0',
`TeleTrac_AccountNumber` varchar(50) DEFAULT NULL,
`TeleTrac_UserName` varchar(50) DEFAULT NULL,
`TeleTrac_Password` varchar(50) DEFAULT NULL,
`UseTeletracYN` tinyint(1) NOT NULL DEFAULT '0',
`PagerSendThroughID` int(11) DEFAULT '1',
`PasswordInvLocDelete` varchar(25) DEFAULT NULL,
`ZMobileEMobilePayYN` tinyint(1) NOT NULL DEFAULT '0',
`GLAXISDisabledYN` tinyint(1) NOT NULL DEFAULT '0',
`AutomaticDownloadWaitTime` int(11) DEFAULT '0',
`EMailAddress` varchar(255) DEFAULT NULL,
`MyTimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`M_FranchiseeName` varchar(255) DEFAULT NULL,
`M_FirstName` varchar(100) DEFAULT NULL,
`M_LastName` varchar(100) DEFAULT NULL,
`M_Address` varchar(255) DEFAULT NULL,
`M_City` varchar(100) DEFAULT NULL,
`M_State` varchar(15) DEFAULT NULL,
`M_Zip` varchar(25) DEFAULT NULL,
`M_Phone` varchar(25) DEFAULT NULL,
`M_Fax` varchar(25) DEFAULT NULL,
`SOSEnabledYN` tinyint(1) NOT NULL DEFAULT '0',
`AttachPOYN` tinyint(1) NOT NULL DEFAULT '1',
`UseMasterInventoryYN` tinyint(1) NOT NULL DEFAULT '0',
`QBVersion` varchar(25) DEFAULT NULL,
`QBLastTransferDate` datetime DEFAULT NULL,
`RouteMethod` varchar(25) DEFAULT NULL,
`RouteMethodDate` datetime DEFAULT NULL,
`RouteLastAcceptDate` datetime DEFAULT NULL,
`PBXCompanyID` int(11) DEFAULT '0',
`PBXFranchiseeID` varchar(100) DEFAULT NULL,
`PBXWebServiceUserName` varchar(100) DEFAULT NULL,
`PBXWebServicePassword` varchar(100) DEFAULT NULL,
`PBXSSLUserName` varchar(100) DEFAULT NULL,
`PBXSSLPassword` varchar(100) DEFAULT NULL,
`PBXCallRecordsLastDateTime` datetime DEFAULT NULL,
`PhoneMonitorEnabledYN` tinyint(1) NOT NULL DEFAULT '0',
`WorkOrderMultiDayEnabledYN` tinyint(1) NOT NULL DEFAULT '0',
`SOSAppointmentStartTimeOffset` int(11) DEFAULT '0',
PRIMARY KEY (`FranchiseID`),
KEY `CompanyStoreID` (`CompanyStoreID`),
KEY `CountryId` (`CountryId`),
KEY `DispatchOpenToID` (`DispatchOpenToID`),
KEY `DoubleBookingID` (`DoubleBookingID`),
KEY `FranchiseID` (`FranchiseID`),
KEY `InstallationTypeID` (`InstallationTypeID`),
KEY `LastTableUpdatesID` (`LastTableUpdatesID`),
KEY `LicNum` (`LicNum`),
KEY `NAGSLicNum` (`NAGSLicNum`),
KEY `QBTransferID` (`QBTransferID`),
KEY `ServerTUID` (`ServerTUID`),
KEY `StatementOrderHeaderID` (`StatementOrderHeaderID`),
KEY `SystemId` (`SystemId`),
KEY `WSAErrorHRID` (`WSAErrorHRID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1"