Optimize MySQL Structure and Query

Question

Changing Question considerably, orig my fault due to being on VPN and not local network of MySQL server, but issue still remains that this query is too slow. My full query is now included and I am adding all layouts for joined tables.

This Query is taking too long, it does return a large dataset, but limiting the dataset doesn't seem to help it. I have tried it with and without the WHERE based on date range.

Its still taking 8 or more seconds to come back. Using LIMIT also doesn't make a difference.

I fear it may be just too many records in each table, in which case my only option would be to create a set of "working" tables containing like the last weeks worth of shipments and then move them over to a set if "history" tables where the queries would all be slow. Unless someone can find a way to make this faster. Thank you.

SELECT 
  ShipmentHeader.RecNbr,
  ShipmentHeader.Void,
  ShipmentHeader.SONum,
  ShipmentHeader.DateofReq,
  ShipmentHeader.ShipToName,
  ShipmentHeader.ShipToCity,
  ShipmentHeader.ShipToState,
  ShipmentHeader.ShipToZip,
  ShipmentHeader.ShipToCountry,
  Carrier.RecNbr AS CarrierRecNbr,
  CarrierService.RecNbr AS CarrierServiceRecNbr,
  ShipmentLabelsPrintLogView.Users 
FROM
  ShipmentHeader 
  INNER JOIN ShipmentLabels 
    ON ShipmentHeader.RecNbr = ShipmentLabels.HeaderRecNbr 
  LEFT JOIN VendorService 
    ON ShipmentLabels.VendorServiceRecNbr = VendorService.RecNbr 
  LEFT JOIN CarrierService 
    ON VendorService.CarrierServiceRecNbr = CarrierService.RecNbr 
  LEFT JOIN Carrier 
    ON CarrierService.CarrierRecNbr = Carrier.RecNbr 
  LEFT JOIN ShipmentLabelsPrintLogView 
    ON ShipmentLabels.RecNbr = ShipmentLabelsPrintLogView.ShipmentLabelsRecNbr 
ORDER BY  ShipmentHeader.RecNbr DESC

Here are the table structures:

CREATE TABLE `ShipmentHeader` (
  `RecNbr` int(11) NOT NULL AUTO_INCREMENT,
  `Void` varchar(1) NOT NULL DEFAULT 'N',
  `SONum` varchar(7) NOT NULL,
  `User` varchar(6) NOT NULL,
  `DateofReq` datetime NOT NULL,
  `ShipToName` varchar(255) DEFAULT NULL,
  `ShipToAddress1` varchar(255) DEFAULT NULL,
  `ShipToAddress2` varchar(255) DEFAULT NULL,
  `ShipToAddress3` varchar(255) DEFAULT NULL,
  `ShipToCity` varchar(255) DEFAULT NULL,
  `ShipToState` varchar(255) DEFAULT NULL,
  `ShipToZip` varchar(255) DEFAULT NULL,
  `ShipToCountry` varchar(255) DEFAULT NULL,
  `PackageHeaderRecNbr` int(11) DEFAULT NULL,
  `NegotiatedShipmentCharge` decimal(10,2) DEFAULT NULL,
  `PublishedShipmentCharge` decimal(10,2) DEFAULT NULL,
  `CustomerShipmentCharge` decimal(10,2) DEFAULT NULL,
  `VoidTimeStamp` datetime DEFAULT NULL,
  `VoidUser` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`RecNbr`),
  KEY `SONum` (`SONum`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=539140 DEFAULT CHARSET=utf8



CREATE TABLE `ShipmentLabels` (
  `RecNbr` int(11) NOT NULL AUTO_INCREMENT,
  `Whse` varchar(3) DEFAULT NULL,
  `HeaderRecNbr` int(11) NOT NULL,
  `PackageRecNbr` int(11) NOT NULL,
  `Carrier` varchar(60) NOT NULL DEFAULT '',
  `Service` varchar(60) DEFAULT NULL,
  `Charges` decimal(10,2) NOT NULL,
  `RatedCost` decimal(10,2) NOT NULL,
  `PublishedRate` decimal(10,2) NOT NULL,
  `CustomerCharge` decimal(10,2) NOT NULL,
  `Weight` int(11) NOT NULL,
  `LabelFormat` varchar(30) NOT NULL DEFAULT '',
  `ShipmentID` varchar(60) NOT NULL,
  `TrackingNumber` varchar(60) NOT NULL DEFAULT '',
  `Label` mediumtext NOT NULL COMMENT 'Base64 Encoded',
  `DateofLabel` datetime NOT NULL,
  `LabelSource` varchar(50) DEFAULT NULL,
  `VendorServiceRecNbr` int(11) DEFAULT NULL,
  PRIMARY KEY (`RecNbr`),
  KEY `HeaderRecNbr` (`HeaderRecNbr`) USING BTREE,
  KEY `PackageRecNbr` (`PackageRecNbr`) USING BTREE,
  CONSTRAINT `ShipmentLabels_ibfk_1` FOREIGN KEY (`HeaderRecNbr`) REFERENCES `ShipmentHeader` (`RecNbr`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=542542 DEFAULT CHARSET=utf8

CREATE TABLE `VendorService` (
  `RecNbr` int(11) NOT NULL AUTO_INCREMENT,
  `CarrierServiceRecNbr` int(11) DEFAULT NULL,
  `VendorRecNbr` int(11) DEFAULT NULL,
  `VendorServiceCode` varchar(255) DEFAULT NULL,
  `VendorServiceDesc` varchar(255) DEFAULT NULL,
  `PackageType` varchar(60) DEFAULT NULL,
  `LabelServiceCode` varchar(255) DEFAULT NULL,
  `LabelPackageType` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`RecNbr`),
  UNIQUE KEY `Unique` (`CarrierServiceRecNbr`,`VendorRecNbr`,`PackageType`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=196 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `CarrierService` (
  `RecNbr` int(11) NOT NULL AUTO_INCREMENT,
  `CarrierRecNbr` int(11) DEFAULT NULL,
  `CarrierServiceCode` varchar(50) DEFAULT NULL,
  `CarrierServiceDesc` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`RecNbr`),
  UNIQUE KEY `unique` (`CarrierRecNbr`,`CarrierServiceCode`)
) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `Carrier` (
  `RecNbr` int(11) NOT NULL AUTO_INCREMENT,
  `CarrierName` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`RecNbr`),
  UNIQUE KEY `unique` (`CarrierName`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

I am joining on this View I created... that seems to be where the issue is... but the Table its based on isn't in the format for data that I need without this view.

CREATE ALGORITHM=UNDEFINED DEFINER=`atr`@`%` SQL SECURITY DEFINER VIEW `ShipmentLabelsPrintLogView` 
AS 
select `A`.`ShipmentLabelsRecNbr` AS `ShipmentLabelsRecNbr`,
  (
    select group_concat(distinct `ShipmentLabelsPrintLog`.`User` 
      order by `ShipmentLabelsPrintLog`.`User` ASC separator ', ') 
    from `ShipmentLabelsPrintLog` 
    where (`ShipmentLabelsPrintLog`.`ShipmentLabelsRecNbr` = `A`.`ShipmentLabelsRecNbr`)
  ) AS `Users` 
from `ShipmentLabelsPrintLog` `A` 
group by `A`.`ShipmentLabelsRecNbr`

The Table the view is based on is here:

CREATE TABLE `ShipmentLabelsPrintLog` (
  `RecNbr` int(11) NOT NULL AUTO_INCREMENT,
  `ShipmentLabelsRecNbr` int(11) NOT NULL,
  `User` varchar(10) NOT NULL,
  `Workstation` varchar(20) NOT NULL,
  `DateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`RecNbr`)
) ENGINE=InnoDB AUTO_INCREMENT=5908 DEFAULT CHARSET=utf8mb4;

Here is the Explain:

id  select_type         table                   type    possible_keys  key           key_len  ref                                          rows     Extra                            
1   PRIMARY             ShipmentHeader          index                  PRIMARY       4                                                     1979351                                   
1   PRIMARY             ShipmentLabels          ref     HeaderRecNbr   HeaderRecNbr  4        Shipping.ShipmentHeader.RecNbr               1                                         
1   PRIMARY             VendorService           eq_ref  PRIMARY        PRIMARY       4        Shipping.ShipmentLabels.VendorServiceRecNbr  1        Using where                      
1   PRIMARY             CarrierService          eq_ref  PRIMARY        PRIMARY       4        Shipping.VendorService.CarrierServiceRecNbr  1        Using where                      
1   PRIMARY             Carrier                 eq_ref  PRIMARY        PRIMARY       4        Shipping.CarrierService.CarrierRecNbr        1        Using where; Using index         
1   PRIMARY                           ref     key0           key0          5        Shipping.ShipmentLabels.RecNbr               10       Using where                      
2   DERIVED             A                       ALL                                                                                        6576     Using temporary; Using filesort  
3   DEPENDENT SUBQUERY  ShipmentLabelsPrintLog  ALL                                                                                        6576     Using where

Is there a way to get the same results faster?

Thanks!

Optimize MySQL Structure and Query

Answers (1)

Related Questions