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…

Squarespace Image Export

To gain continued access to your Squarespace website images after cancelling your subscription you have several...

MySQL 8.x GRANT ALL STATEMENT

-- CREATE CREATE USER 'tgnrestoreuser'@'localhost' IDENTIFIED BY 'AppleSauceLoveBird2024'; GRANT ALL PRIVILEGES ON...

Exetel Opt-Out of CGNAT

If your port forwards and inbound and/or outbound site-to-site VPN's have failed when switching to Exetel due to their...