Mysql – Optimise primary key and index settings for multiple link tables

I have this tables stated as below.

1. orders is the main table that will store all the details such as the customerID, merchantID, which riderID it was assign. The order details like total, subTotal, tax and also customer details like address latitude, longitude. It also saves every detail like when the order was accepted, ready, delivered.

CREATE TABLE `orders` (
  `orderID` int UNSIGNED NOT NULL,
  `orderIDHash` varchar(10) NOT NULL,
  `customerID` mediumint UNSIGNED NOT NULL,
  `merchantID` smallint UNSIGNED NOT NULL,
  `branchID` smallint UNSIGNED NOT NULL,
  `riderID` smallint UNSIGNED NOT NULL,
  `orderDateTime` timestamp NOT NULL,
  `subTotal` decimal(10,0) NOT NULL,
  `tax` decimal(10,0) NOT NULL,
  `total` decimal(10,0) NOT NULL,
  `quantity` tinyint NOT NULL,
  `customerAddress` varchar(150) NOT NULL,
  `customerLatitude` float NOT NULL,
  `customerLongitude` float NOT NULL,
  `paymentID` varchar(30) NOT NULL,
  `paymentType` tinyint NOT NULL,
  `instructions` varchar(100) NOT NULL,
  `orderType` tinyint NOT NULL,
  `neworderNotify` tinyint NOT NULL,
  `orderAcceptedDateTime` timestamp NOT NULL,
  `merchantUserAcceptedID` smallint NOT NULL DEFAULT '0',
  `orderAcceptedNotify` tinyint NOT NULL DEFAULT '0',
  `orderReadyDateTime` timestamp NOT NULL,
  `merchantUserReadyID` smallint NOT NULL DEFAULT '0',
  `orderReadyNotify` tinyint NOT NULL DEFAULT '0',
  `orderInDeliveryDateTime` timestamp NOT NULL,
  `orderInDeliveryNotify` tinyint NOT NULL DEFAULT '0',
  `orderPickerOrDeliveryDateTime` timestamp NOT NULL,
  `orderCancelDateTime` timestamp NOT NULL,
  `orderStatus` tinyint NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `orders`
  ADD PRIMARY KEY (`orderID`),
  ADD UNIQUE KEY `orderIDHash` (`orderIDHash`),
  ADD KEY `customerID` (`customerID`),
  ADD KEY `merchantID` (`merchantID`),
  ADD KEY `riderID` (`riderID`),
  ADD KEY `branchID` (`branchID`),
  ADD KEY `orderStatus` (`orderStatus`),
  ADD KEY `orderAcceptedNotify` (`orderAcceptedNotify`),
  ADD KEY `orderReadyNotify` (`orderReadyNotify`),
  ADD KEY `orderInDeliveryNotify` (`orderInDeliveryNotify`);


ALTER TABLE `orders`
  MODIFY `orderID` int UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

2. The second table is the ordersLine. The table basically store all the items related to one particular order. Thus its related to the order table above.

CREATE TABLE `orderLine` (
  `orderID` int NOT NULL,
  `itemID` mediumint NOT NULL,
  `itemQuantity` tinyint NOT NULL,
  `itemPrice` decimal(10,0) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `orderLine`
  ADD PRIMARY KEY (`orderID`,`itemID`);
COMMIT;

3. CustomerMessageLog is basically for example is the order table above we have this field orderAcceptedNotify which will change its value say default is 0 then to 1 meaning order is accepted by merchant and to 2 when its already notified to the user. Thus we store the message in this log so when user log he can see all the message. There can be different messageID for different type of messages.

CREATE TABLE `customerMessageLog` (
  `orderID` int NOT NULL,
  `customerID` mediumint NOT NULL,
  `messageID` tinyint NOT NULL,
  `messageDateTime` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


ALTER TABLE `customerMessageLog`
  ADD PRIMARY KEY (`orderID`,`customerID`,`messageID`);
COMMIT;

4. This table is to store all the notified message to the rider.

CREATE TABLE `riderMessageLog` (
  `orderID` int NOT NULL,
  `riderID` smallint NOT NULL,
  `messageID` tinyint NOT NULL,
  `messageDateTime` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
ALTER TABLE `riderMessageLog`
  ADD PRIMARY KEY (`orderID`,`riderID`,`messageID`);
COMMIT;

5. This table is to store all the notified message to the merchant.

CREATE TABLE `merchantMessageLog` (
  `orderID` int NOT NULL,
  `merchantID` smallint NOT NULL,
  `branchID` smallint NOT NULL,
  `messageID` tinyint NOT NULL,
  `messageDateTime` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


ALTER TABLE `merchantMessageLog`
  ADD PRIMARY KEY (`orderID`,`merchantID`,`branchID`,`messageID`);
COMMIT;

6. This is the table which will store all the orderStatus and also messageID in most of the tables above refer to this table via join.

CREATE TABLE `statusMessage` (
  `statusID` tinyint UNSIGNED NOT NULL,
  `message` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `statusMessage`
--

INSERT INTO `statusMessage` (`statusID`, `message`) VALUES
(0, 'Pending'),
(1, 'Payment Completed'),
(2, 'Accepted'),
(3, 'Completed'),
(4, 'Self Picked Up'),
(5, 'Assigned Rider'),
(6, 'In Delivery'),
(7, 'Delivered');


ALTER TABLE `statusMessage`
  ADD PRIMARY KEY (`statusID`);

So now the challenge is that I have set the orderID to be DDMMYYHHMMSS which I get from the user phone before order is submitted for payment. Why I did this is cause for the payment gateway I will need to orderID.On the other hand I could also set it as auto increment and can also insert all the order and orderLine before payment but if the payment fails then that is another issue how to handled the inserted order and orderLine. So which is most effective method. Also I would like to hash the primary when its shown to user?