Written by James McDonald

January 8, 2020

Just putting this here as I have a SELECT in MySQL that was taking 1.946 sec to return 20 records from a table with 159884 records when using a ORDER BY DESC on a DATETIME field

Creating an index and changing the FROM to FORCE INDEX () dropped it down to 0.00079 sec / 0.000042 sec

SELECT 
    `Pallet`.`id`,
    `Pallet`.`inventory_status_id`,
    `Pallet`.`item_id`,
    `Pallet`.`location_id`,
    `Pallet`.`description`,
    `Pallet`.`item`,
    `Pallet`.`bb_date`,
    `Pallet`.`gtin14`,
    `Pallet`.`qty`,
    `Pallet`.`qty_previous`,
    `Pallet`.`qty_modified`,
    `Pallet`.`pl_ref`,
    `Pallet`.`sscc`,
    `Pallet`.`batch`,
    `Pallet`.`printer`,
    `Pallet`.`print_date`,
    `Pallet`.`cooldown_date`,
    `Pallet`.`min_days_life`,
    `Pallet`.`production_line`,
    `Pallet`.`printer_id`,
    `Pallet`.`product_type_id`,
    `Pallet`.`qty_user_id`,
    `Pallet`.`shipment_id`,
    `Pallet`.`inventory_status_note`,
    `Pallet`.`inventory_status_datetime`,
    `Pallet`.`created`,
    `Pallet`.`modified`,
    `Pallet`.`ship_low_date`,
    `Pallet`.`picked`,
    `Pallet`.`production_line_id`,
    unix_timestamp(`Pallet`.`print_date`) as PRINT_DATE_SORT,
    (CONCAT(`Pallet`.`pl_ref`,
            ': ',
            `Pallet`.`item`,
            ' - ',
            `Pallet`.`description`)) AS `Pallet__code_desc`,
    (DATE_FORMAT(`Pallet`.`bb_date`, '%d/%m/%y')) AS `Pallet__best_before`,
    (CONCAT(`Pallet`.`item`,
            ' ',
            `Pallet`.`description`,
            ' ',
            `Pallet`.`pl_ref`)) AS `Pallet__name`,
    (DATEDIFF(`Pallet`.`bb_date`, CURDATE()) < `Pallet`.`min_days_life`
        AND `Pallet`.`shipment_id` = 0) AS `Pallet__dont_ship`,
    `Location`.`id`,
    `Location`.`location`,
    `Location`.`pallet_capacity`,
    `Location`.`is_hidden`,
    `Location`.`description`,
    `Location`.`created`,
    `Location`.`modified`,
    `Location`.`product_type_id`,
    `Shipment`.`id`,
    `Shipment`.`operator_id`,
    `Shipment`.`truck_registration_id`,
    `Shipment`.`shipper`,
    `Shipment`.`con_note`,
    `Shipment`.`shipment_type`,
    `Shipment`.`destination`,
    `Shipment`.`pallet_count`,
    `Shipment`.`shipped`,
    `Shipment`.`time_start`,
    `Shipment`.`time_finish`,
    `Shipment`.`time_total`,
    `Shipment`.`truck_temp`,
    `Shipment`.`dock_temp`,
    `Shipment`.`product_temp`,
    `Shipment`.`created`,
    `Shipment`.`modified`,
    `Shipment`.`product_type_id`,
    `InventoryStatus`.`id`,
    `InventoryStatus`.`perms`,
    `InventoryStatus`.`name`,
    `InventoryStatus`.`comment`
FROM
    `palletsUpgrade`.`pallets` AS `Pallet` FORCE INDEX(print_date)
        LEFT JOIN
    `palletsUpgrade`.`locations` AS `Location` ON (`Pallet`.`location_id` = `Location`.`id`)
        LEFT JOIN
    `palletsUpgrade`.`shipments` AS `Shipment` ON (`Pallet`.`shipment_id` = `Shipment`.`id`)
        LEFT JOIN
    `palletsUpgrade`.`inventory_statuses` AS `InventoryStatus` ON (`Pallet`.`inventory_status_id` = `InventoryStatus`.`id`)
WHERE
    1 = 1
ORDER BY `Pallet`.`print_date` DESC 
LIMIT 20
CREATE TABLE `pallets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `inventory_status_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL,
  `description` varchar(50) CHARACTER SET latin1 NOT NULL,
  `item` varchar(10) CHARACTER SET latin1 NOT NULL,
  `best_before` varchar(10) CHARACTER SET latin1 NOT NULL,
  `bb_date` date NOT NULL,
  `gtin14` varchar(14) CHARACTER SET latin1 NOT NULL,
  `qty` int(5) NOT NULL,
  `qty_previous` varchar(255) CHARACTER SET latin1 NOT NULL,
  `qty_modified` datetime NOT NULL,
  `pl_ref` varchar(10) CHARACTER SET latin1 NOT NULL,
  `sscc` varchar(18) CHARACTER SET latin1 NOT NULL,
  `batch` varchar(6) CHARACTER SET latin1 NOT NULL,
  `printer` varchar(50) CHARACTER SET latin1 NOT NULL,
  `print_date` datetime NOT NULL,
  `cooldown_date` datetime DEFAULT NULL,
  `min_days_life` int(11) NOT NULL,
  `production_line` varchar(10) CHARACTER SET latin1 NOT NULL,
  `printer_id` int(11) DEFAULT NULL,
  `product_type_id` int(11) DEFAULT NULL,
  `qty_user_id` int(11) NOT NULL,
  `shipment_id` int(11) NOT NULL,
  `inventory_status_note` varchar(100) NOT NULL,
  `inventory_status_datetime` datetime NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `ship_low_date` tinyint(1) NOT NULL,
  `picked` tinyint(1) NOT NULL,
  `production_line_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pl_ref` (`pl_ref`),
  UNIQUE KEY `sscc` (`sscc`),
  KEY `item` (`item`),
  KEY `item_id` (`item_id`),
  KEY `description` (`description`),
  KEY `bb_date` (`bb_date`),
  KEY `batch` (`batch`),
  KEY `qty` (`qty`),
  KEY `item_id_desc` (`item_id`),
  KEY `qty_desc` (`qty`),
  KEY `bb_date_desc` (`bb_date`),
  KEY `location_id` (`location_id`),
  KEY `location_id_desc` (`location_id`),
  KEY `shipment_id` (`shipment_id`),
  KEY `shipment_id_desc` (`shipment_id`),
  KEY `inventory_status_id` (`inventory_status_id`),
  KEY `print_date` (`print_date`)
) ENGINE=InnoDB AUTO_INCREMENT=159900 DEFAULT CHARSET=utf8;

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

The reCAPTCHA verification period has expired. Please reload the page.

You May Also Like…

Clear HSTS Settings in CHrome

Open chrome://net-internals/#hsts enter the domain in the query field and click Query to confirm it has HSTS settings...

Ubuntu on Hyper-v

It boils town to installing linux-azure # as root or sudo apt-get update apt-get install linux-azure...