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