TransWikia.com

mysql upgrade 5.0.45 to 5.1.7 and problem query

Database Administrators Asked by vivvvi on October 28, 2021

Have been working on a migration the past few weeks, and we have run into a mysql specific problem.

After creating a new VM, faster server than the old, we have a query that takes approx 2 seconds on the old server (mysql 5.0.45) and takes over 3mins on the new server.

After much testing we noticed the CPU was becoming I/O bound thanks to CentOS not using all the cores, and have been tuning mysql since.

I’ve done about all the tuning of the server I can, and noticed that the query on the new server appearing in the slow.log is doing WAY more work..

Check out the rows examined!

The data in both databases are the same, as is the query.

QUERY:

SELECT ba.ID, bi.buildingApplicationId, oa.`status`,  ba.ApplicantName,
   CONCAT(ba.No,' ',ba.Street,' ', ba.Suburb,' ',ba.STATE) as address,
   ba.PC, bi.fileId,  bi.`type`,
   (select CONCAT(`status`, '@', `date`, '@', `filename`)
          from tblBuildingAppsInspectionFile
          where buildingApplicationId=bi.buildingApplicationId
            and `type` = bi.`type`
          order by fileId desc limit 0,1
   ) as status_date_file
                        FROM building_application ba
                        LEFT JOIN occupancy_application oa ON (ba.ID = oa.ID)
                        LEFT JOIN `tblBuildingAppsInspectionFile` bi  ON (bi.buildingApplicationId = ba.ID)
                        WHERE (        ba.isArchive='0'
                                               AND ba.`Status`='complete'
                                               AND ba.DateFinalised >= DATE_SUB(CURDATE(),INTERVAL 2 YEAR)
                                               AND ba.DateFinalised <= CURDATE()
                                               AND ba.`Date` >= DATE_SUB(CURDATE(),INTERVAL 50 MONTH)
                                               AND ba.`Date` <= CURDATE()
                                               AND (oa.`Status` IS NULL OR oa.`Status` != 'complete')
                                       )
                        GROUP BY ba.ID, bi.type
                        ORDER BY ba.ID, bi.type;

OLD Server:

# Time: 170112  1:31:12
# Query_time: 2  Lock_time: 0  Rows_sent: 8566  Rows_examined: 166213

New Server:

# Time: 170112  1:34:41
# Query_time: 204.556346  Lock_time: 0.000160 Rows_sent: 8663  Rows_examined: 338808210

the query is a dog of a thing, I know.. and we don’t have indexes installed on either DB (yet) but can someone explain why so many rows are examined on the new setup?

I suspect the JOINS and the way mysql 5.1 deals with them might be the culprit, but I am no DB expert.

Thanks in advance…..

Further to discussions:

actually we do have indexes, maybe too many.

TABLE_NAME      INDEX_NAME
active_guests   PRIMARY
active_users    PRIMARY
approval_type   PRIMARY
assoc_approvals PRIMARY
banned_users    PRIMARY
bp_apr  PRIMARY
bp_apr  No
bp_apr  PC
bp_apr  Street
bp_apr  No_2
bp_nc   PRIMARY
bp_nc   LoginID
bp_nc   No
bp_nc   Street
bp_nc   Suburb
building_application    PRIMARY
building_application    LoginID
building_application    ID
building_application    No
building_application    Street
building_application    Suburb
building_application_uploads    PRIMARY
building_application_uploads    Name
building_checklist      PRIMARY
building_checklist      AppID
building_checklist_old  PRIMARY
building_checklist_old  AppID
building_classification PRIMARY
companies       PRIMARY
cp_admin        PRIMARY
cp_building_apps_document_log_entry     PRIMARY
cp_building_apps_document_log_entry     userId
cp_building_apps_document_log_entry     actionType
cp_building_apps_document_log_entry     logDate
cp_building_apps_document_log_entry     appId
cp_building_apps_document_log_entry     action
cp_building_apps_document_log_entry     notificationDate
cp_building_apps_inspection_type        PRIMARY
cp_building_apps_inspection_type        title
cp_cms_menu     PRIMARY
cp_companies    PRIMARY
cp_company_type PRIMARY

There are a few reasons why moving past 5.1.7 isn’t practical, but if that’s what is required then we will do it..

also I noticed the query doesn’t use the AS clause.

-- Use the keyword AS.
SELECT CategoryID AS ID, CategoryName AS Name 
FROM categories;

-- Keyword AS is not used - Not recommended.
SELECT CategoryID ID, CategoryName Name 
FROM categories;

can anyone tell me why it is not recommended and if it in itself may cause a problem.

as requested:

SHOW CREATE TABLE (OLD):

building_application | CREATE TABLE `building_application` (
  `ID` int(6) NOT NULL auto_increment,
  `LoginID` smallint(5) unsigned NOT NULL default '0',
  `No` varchar(15) default NULL,
  `Street` varchar(255) default NULL,
  `Suburb` varchar(255) default NULL,
  `PC` int(4) default NULL,
  `State` enum('ACT','NSW','NT','QLD','VIC','SA','TAS','WA') default NULL,
  `City` varchar(255) default NULL,
  `RefNo` varchar(20) default NULL,
  `StoreName` varchar(40) default NULL,
  `ProposedDate` date default NULL,
  `InspectionDate` date default NULL,
  `BuildingArea` int(6) default NULL,
  `AllotmentArea` int(6) default NULL,
  `ApplicantName` varchar(100) default NULL,
  `ApplicantTele` varchar(20) default NULL,
  `ApplicantAddress` varchar(255) default NULL,
  `ApplicantFax` varchar(20) default NULL,
  `ApplicantContact` varchar(100) default NULL,
  `ApplicantMobile` varchar(20) default NULL,
  `DocumentName` varchar(100) default NULL,
  `DocumentTele` varchar(20) default NULL,
  `DocumentAddress` varchar(255) default NULL,
  `DocumentFax` varchar(20) default NULL,
  `DocumentContact` varchar(100) default NULL,
  `DocumentMobile` varchar(20) default NULL,
  `OwnerName` varchar(100) default NULL,
  `OwnerTele` varchar(20) default NULL,
  `OwnerAddress` varchar(255) default NULL,
  `OwnerFax` varchar(20) default NULL,
  `OwnerContact` varchar(100) default NULL,
  `OwnerEmail` varchar(100) default NULL,
  `OwnerMobile` varchar(20) default NULL,
  `BuilderName` varchar(100) default NULL,
  `BuilderTele` varchar(20) default NULL,
  `BuilderAddress` varchar(255) default NULL,
  `BuilderFax` varchar(20) default NULL,
  `BuilderContact` varchar(100) default NULL,
  `BuilderMobile` varchar(20) default NULL,
  `BuilderType` enum('Domestic','Owner') default 'Domestic',
  `PractitionerWorkName` varchar(100) default NULL,
  `PractitionerWorkClass` varchar(50) default NULL,
  `PractitionerWorkReg` varchar(20) default NULL,
  `PractitionerDocumentsName` varchar(100) default NULL,
  `PractitionerDocumentsClass` varchar(50) default NULL,
  `PractitionerDocumentsReg` varchar(20) default NULL,
  `PractitionerDocumentsName2` varchar(100) default NULL,
  `PractitionerDocumentsClass2` varchar(50) default NULL,
  `PractitionerDocumentsReg2` varchar(20) default NULL,
  `NatureOther` varchar(255) default NULL,
  `DomesticBuilderWarranty` text,
  `OwnerBuilderCert` text,
  `OwnerBuilderWarranty` text,
  `BuildingUse` varchar(255) default NULL,
  `BCAClass` varchar(20) default NULL,
  `ValueOfWork` varchar(7) default NULL,
  `StageOfWork` varchar(255) default NULL,
  `Signature` varchar(100) default NULL,
  `Fees` enum('cash','cheque','visa') default NULL,
  `DeclarationType` enum('Owner','Agent') default NULL,
  `Date` date default NULL,
  `Status` enum('incomplete','complete','processing','cancelled','archived') NOT NULL default 'incomplete',
  `StatusDescription` text,
  `DateFinalised` date default NULL,
  `OccupancyStatus` enum('Uncommenced','Commenced') NOT NULL default 'Uncommenced',
  `cpAmended` timestamp NULL default CURRENT_TIMESTAMP,
  `isArchive` tinyint(1) default '0',
  `archiveDate` datetime default NULL,
  `stopWorksFlag` int(1) NOT NULL default '0',
  PRIMARY KEY  (`ID`),
  KEY `LoginID` (`LoginID`),
  KEY `ID` (`ID`),
  FULLTEXT KEY `No` (`No`),
  FULLTEXT KEY `Street` (`Street`),
  FULLTEXT KEY `Suburb` (`Suburb`)
) ENGINE=MyISAM AUTO_INCREMENT=xxxxx DEFAULT CHARSET=utf8

SHOW CREATE TABLE (NEW):

building_application | CREATE TABLE `building_application` (
  `ID` int(6) NOT NULL AUTO_INCREMENT,
  `LoginID` smallint(5) unsigned NOT NULL DEFAULT '0',
  `No` varchar(15) DEFAULT NULL,
  `Street` varchar(255) DEFAULT NULL,
  `Suburb` varchar(255) DEFAULT NULL,
  `PC` int(4) DEFAULT NULL,
  `State` enum('ACT','NSW','NT','QLD','VIC','SA','TAS','WA') DEFAULT NULL,
  `City` varchar(255) DEFAULT NULL,
  `RefNo` varchar(20) DEFAULT NULL,
  `StoreName` varchar(40) DEFAULT NULL,
  `ProposedDate` date DEFAULT NULL,
  `InspectionDate` date DEFAULT NULL,
  `BuildingArea` int(6) DEFAULT NULL,
  `AllotmentArea` int(6) DEFAULT NULL,
  `ApplicantName` varchar(100) DEFAULT NULL,
  `ApplicantTele` varchar(20) DEFAULT NULL,
  `ApplicantAddress` varchar(255) DEFAULT NULL,
  `ApplicantFax` varchar(20) DEFAULT NULL,
  `ApplicantContact` varchar(100) DEFAULT NULL,
  `ApplicantMobile` varchar(20) DEFAULT NULL,
  `DocumentName` varchar(100) DEFAULT NULL,
  `DocumentTele` varchar(20) DEFAULT NULL,
  `DocumentAddress` varchar(255) DEFAULT NULL,
  `DocumentFax` varchar(20) DEFAULT NULL,
  `DocumentContact` varchar(100) DEFAULT NULL,
  `DocumentMobile` varchar(20) DEFAULT NULL,
  `OwnerName` varchar(100) DEFAULT NULL,
  `OwnerTele` varchar(20) DEFAULT NULL,
  `OwnerAddress` varchar(255) DEFAULT NULL,
  `OwnerFax` varchar(20) DEFAULT NULL,
  `OwnerContact` varchar(100) DEFAULT NULL,
  `OwnerEmail` varchar(100) DEFAULT NULL,
  `OwnerMobile` varchar(20) DEFAULT NULL,
  `BuilderName` varchar(100) DEFAULT NULL,
  `BuilderTele` varchar(20) DEFAULT NULL,
  `BuilderAddress` varchar(255) DEFAULT NULL,
  `BuilderFax` varchar(20) DEFAULT NULL,
  `BuilderContact` varchar(100) DEFAULT NULL,
  `BuilderMobile` varchar(20) DEFAULT NULL,
  `BuilderType` enum('Domestic','Owner') DEFAULT 'Domestic',
  `PractitionerWorkName` varchar(100) DEFAULT NULL,
  `PractitionerWorkClass` varchar(50) DEFAULT NULL,
  `PractitionerWorkReg` varchar(20) DEFAULT NULL,
  `PractitionerDocumentsName` varchar(100) DEFAULT NULL,
  `PractitionerDocumentsClass` varchar(50) DEFAULT NULL,
  `PractitionerDocumentsReg` varchar(20) DEFAULT NULL,
  `PractitionerDocumentsName2` varchar(100) DEFAULT NULL,
  `PractitionerDocumentsClass2` varchar(50) DEFAULT NULL,
  `PractitionerDocumentsReg2` varchar(20) DEFAULT NULL,
  `NatureOther` varchar(255) DEFAULT NULL,
  `DomesticBuilderWarranty` text,
  `OwnerBuilderCert` text,
  `OwnerBuilderWarranty` text,
  `BuildingUse` varchar(255) DEFAULT NULL,
  `BCAClass` varchar(20) DEFAULT NULL,
  `ValueOfWork` varchar(7) DEFAULT NULL,
  `StageOfWork` varchar(255) DEFAULT NULL,
  `Signature` varchar(100) DEFAULT NULL,
  `Fees` enum('cash','cheque','visa') DEFAULT NULL,
  `DeclarationType` enum('Owner','Agent') DEFAULT NULL,
  `Date` date DEFAULT NULL,
  `Status` enum('incomplete','complete','processing','cancelled','archived') NOT                                                          NULL DEFAULT 'incomplete',
  `StatusDescription` text,
  `DateFinalised` date DEFAULT NULL,
  `OccupancyStatus` enum('Uncommenced','Commenced') NOT NULL DEFAULT 'Uncommence                                                         d',
  `cpAmended` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `isArchive` tinyint(1) DEFAULT '0',
  `archiveDate` datetime DEFAULT NULL,
  `stopWorksFlag` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `LoginID` (`LoginID`),
  KEY `ID` (`ID`),
  FULLTEXT KEY `No` (`No`),
  FULLTEXT KEY `Street` (`Street`),
  FULLTEXT KEY `Suburb` (`Suburb`)
) ENGINE=MyISAM AUTO_INCREMENT=xxxxx DEFAULT CHARSET=utf8

EXPLAIN SELECT… OLD:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: ba
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 37281
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: oa
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: <DBNAME>.ba.ID
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: PRIMARY
        table: bi
         type: ref
possible_keys: buildingApplicationId
          key: buildingApplicationId
      key_len: 4
          ref: <DBNAME>.ba.ID
         rows: 2
        Extra:
*************************** 4. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: tblBuildingAppsInspectionFile
         type: ref
possible_keys: buildingApplicationId
          key: buildingApplicationId
      key_len: 4
          ref: func
         rows: 2
        Extra: Using where; Using filesort
4 rows in set (0.00 sec)

EXPLAIN SELECT… NEW:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: ba
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 37397
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: oa
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: <DBNAME>.ba.ID
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: PRIMARY
        table: bi
         type: ref
possible_keys: buildingApplicationId
          key: buildingApplicationId
      key_len: 4
          ref: <DBNAME>.ba.ID
         rows: 2
        Extra:
*************************** 4. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: tblBuildingAppsInspectionFile
         type: index
possible_keys: buildingApplicationId
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using where
4 rows in set (0.00 sec)
mysqlmysql 5.0mysql 5.1performancequery performanceslow log

One Answer

"No indexes yet". Well, that is the problem. Without indexes, it will scan all of ba. Then, for each of those rows, it will scan every row of oa and bi.

With indexes (depending, of course on what indexes), it will scan only part of ba, then reach into selected parts of the other two tables.

Answered by Rick James on October 28, 2021

Add your own answers!

Ask a Question

Get help from others!

Recent Questions

Recent Answers

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP