Use FORCE INDEX

by | Jan 8, 2020 | IT Tips, Linux Tools, Open Source Apps | 0 comments

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
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
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
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.