Увеличете цялостната производителност на заявката

Боря се да подобря цялостната производителност на моята заявка, тъй като не съм професионалист, когато става въпрос за писане на заявки или оптимизирането им чрез индекси. И в двата случая разгледайте моя SQL Fiddle тук за точна проба. Търся начин (ако има такъв) да използвам индекси при изпълнение на заявката за по-бързо изпълнение (данните към момента са малко, но в крайна сметка ще растат и заявката ще работи бавно - от което се страхувам).

По-долу е текущият ми EXPLAIN резултат:

Допълнителна информация по-долу, която съдържа цялата схема и заявка:

DROP TABLE IF EXISTS `battery_batch`;
CREATE TABLE `battery_batch`  (
  `bb_id` int(11) NOT NULL AUTO_INCREMENT,
  `cc_id` int(11) NOT NULL,
  `batch_num` int(11) NOT NULL,
  `date_encoded` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `encoded_by_id` int(11) NOT NULL,
  PRIMARY KEY (`bb_id`) USING BTREE,
  INDEX `batch_num`(`batch_num`) USING BTREE,
  INDEX `cc_id`(`cc_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `battery_batch` VALUES (1, 5, 1, '2018-04-12 09:35:37', 1);

DROP TABLE IF EXISTS `battery_inbound_shipment`;
CREATE TABLE `battery_inbound_shipment`  (
  `bis_id` int(11) NOT NULL AUTO_INCREMENT,
  `bb_id` int(11) NOT NULL,
  `bpn_id` int(11) NOT NULL,
  `order_num` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `shipment_id` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `encoded_by_id` int(11) NOT NULL,
  `date_encoded` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`bis_id`) USING BTREE,
  INDEX `bb_id`(`bb_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `battery_inbound_shipment` VALUES (1, 1, 1, 'RTS1', 'SID1', 1, '2018-04-14 18:48:48');
INSERT INTO `battery_inbound_shipment` VALUES (2, 1, 2, 'RTS1', 'SID1', 1, '2018-04-14 18:48:50');
INSERT INTO `battery_inbound_shipment` VALUES (3, 1, 3, 'RTS2', 'SID1', 1, '2018-04-14 18:48:53');

DROP TABLE IF EXISTS `battery_part_num`;
CREATE TABLE `battery_part_num`  (
  `bpn_id` int(11) NOT NULL AUTO_INCREMENT,
  `mb_id` int(11) NOT NULL,
  `part_num` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `part_desc` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `encoded_by_id` int(11) NOT NULL,
  `date_encoded` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`bpn_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `battery_part_num` VALUES (1, 71, '6424B', 'NEW PRODUCT', 1, '2018-04-14 18:48:48');
INSERT INTO `battery_part_num` VALUES (2, 51, '6422B', 'NEW PRODUCT', 1, '2018-04-14 18:48:50');
INSERT INTO `battery_part_num` VALUES (3, 71, '6423B', 'NEW PRODUCT', 1, '2018-04-14 18:48:52');

DROP TABLE IF EXISTS `battery_record`;
CREATE TABLE `battery_record`  (
  `br_id` int(11) NOT NULL AUTO_INCREMENT,
  `bis_id` int(11) NOT NULL,
  `battery_serial` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `date_encoded` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `encoded_by_id` int(11) NOT NULL,
  `battery_origin` enum('A Battery','B Battery') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'A Battery',
  `void_record_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`br_id`) USING BTREE,
  INDEX `battery_serial`(`battery_serial`) USING BTREE,
  INDEX `battery_serial_search`(`battery_serial`, `void_record_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 64522 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `battery_record` VALUES (2, 1, 'ABC74661V8J1234CV', '2018-04-14 18:48:48', 1, 'A Battery', 367113);
INSERT INTO `battery_record` VALUES (4, 1, 'ABC747307ER1234CV', '2018-04-14 18:48:48', 1, 'A Battery', 367113);
INSERT INTO `battery_record` VALUES (6, 1, 'ABC802714SQ1234C6', '2018-04-14 18:48:48', 1, 'A Battery', 367113);
INSERT INTO `battery_record` VALUES (8, 1, 'ABC802714SS1234C4', '2018-04-14 18:48:48', 1, 'A Battery', 367113);
INSERT INTO `battery_record` VALUES (10, 1, 'ABC80270F6H1234CX', '2018-04-14 18:48:48', 1, 'A Battery', 367113);
INSERT INTO `battery_record` VALUES (11, 1, 'ABC802714SU1234C2', '2018-04-14 18:48:48', 1, 'A Battery', 367113);
INSERT INTO `battery_record` VALUES (12, 1, 'ABC802714SN1234C8', '2018-04-14 18:48:48', 1, 'A Battery', 367113);
INSERT INTO `battery_record` VALUES (13, 1, 'ABC80270PPM1234C2', '2018-04-14 18:48:48', 1, 'A Battery', 367113);
INSERT INTO `battery_record` VALUES (14, 1, 'ABC80270PPH1234C6', '2018-04-14 18:48:48', 1, 'A Battery', 367113);
INSERT INTO `battery_record` VALUES (15, 1, 'ABC80270QZT1234CZ', '2018-04-14 18:48:48', 1, 'A Battery', 367113);
INSERT INTO `battery_record` VALUES (16, 1, 'ABC80270V191234C6', '2018-04-14 18:48:48', 1, 'A Battery', 367113);
INSERT INTO `battery_record` VALUES (17, 1, 'ABC80270V1M1234CU', '2018-04-14 18:48:48', 1, 'A Battery', 367113);
INSERT INTO `battery_record` VALUES (18, 1, 'ABC80270EN71234CU', '2018-04-14 18:48:48', 1, 'A Battery', 367113);
INSERT INTO `battery_record` VALUES (19, 1, 'ABC80270V1D1234C2', '2018-04-14 18:48:48', 1, 'A Battery', 367113);
INSERT INTO `battery_record` VALUES (20, 1, 'ABC80270F3J1234C5', '2018-04-14 18:48:48', 1, 'A Battery', 367113);

DROP TABLE IF EXISTS `battery_verified_record`;
CREATE TABLE `battery_verified_record`  (
  `bvr_id` int(11) NOT NULL AUTO_INCREMENT,
  `br_id` int(11) NOT NULL,
  `u_id` int(11) NOT NULL,
  `scan_date` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`bvr_id`) USING BTREE,
  INDEX `br_id`(`br_id`) USING BTREE,
  UNIQUE INDEX `scan_date`(`scan_date`, `br_id`) USING BTREE,
  INDEX `u_id`(`u_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `battery_verified_record` VALUES (1, 2, 1, '2018-04-15 11:20:09');
INSERT INTO `battery_verified_record` VALUES (2, 4, 1, '2018-04-15 11:54:00');
INSERT INTO `battery_verified_record` VALUES (3, 6, 1, '2018-04-15 12:23:55');
INSERT INTO `battery_verified_record` VALUES (4, 8, 1, '2018-04-15 12:23:57');
INSERT INTO `battery_verified_record` VALUES (5, 10, 1, '2018-04-15 12:23:58');

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Username` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `Password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `PasswordExpirationDate` datetime(6) NOT NULL,
  `FirstName` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `LastName` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `LocationId` int(11) NOT NULL,
  `IsActive` tinyint(1) NOT NULL,
  `IsDeleted` tinyint(1) NOT NULL,
  `UserTypeId` int(11) NOT NULL,
  `VendorId` int(11) NULL DEFAULT NULL,
  `ResetPassword` tinyint(1) NOT NULL DEFAULT 0,
  `Workerid` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `ComputerProcessMappingId` int(11) NOT NULL,
  PRIMARY KEY (`Id`) USING BTREE,
  INDEX `LastName`(`LastName`, `FirstName`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 489 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `user` VALUES (1, 'superadmin', '1000:Q8v+4nGsGUGvmvlCkhdWI0Sf+ABCDEFG+YY:vHkHPWNrvLyX5pUm8Q4HrmJlTkZhpvZL', '2017-02-07 09:41:04.000000', 'Super', 'Admin', 4, 1, 0, 1, NULL, 0, '1', 0);

DROP TABLE IF EXISTS `customer_client`;
CREATE TABLE `customer_client`  (
  `cc_id` int(11) NOT NULL AUTO_INCREMENT,
  `client_code` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `client_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'NOT AVAILABLE',
  `date_encoded` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cc_id`) USING BTREE,
  UNIQUE INDEX `tmp_1`(`client_code`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 85 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `customer_client` VALUES (5, 'MNO - 05', 'NOT AVAILABLE', '2018-03-26 14:28:22');





####


SELECT
    br.battery_serial 'BatterySerial',
    cc.client_code 'Client',
    bb.batch_num 'Batch',
    CONCAT(u.LastName, ', ', u.FirstName) 'VerifiedBy',
    bvr.scan_date 'ScanDate'
FROM
    battery_verified_record bvr
JOIN
    battery_record br
ON
    br.br_id = bvr.br_id
JOIN
    battery_inbound_shipment bis
ON
    bis.bis_id = br.bis_id
JOIN
    battery_batch bb
ON
    bb.bb_id = bis.bb_id
JOIN
    customer_client cc
ON
    cc.cc_id = bb.cc_id
JOIN
    user u
ON
    u.Id = bvr.u_id
WHERE
    bvr.scan_date BETWEEN CONCAT(DATE_FORMAT(NOW(), '%Y-%m-%d'), ' 00:00:00') AND NOW()
ORDER BY
    bvr.scan_date DESC;