Advanced MySQL Topics - Database Optimization

CREATE TABLE Employees(EmployeeID INT DEFAULT NULL, FullName VARCHAR(100) DEFAULT NULL, Role VARCHAR(50) DEFAULT NULL, Department VARCHAR(255) DEFAULT NULL);

CREATE TABLE Activity (ActivityID INT PRIMARY KEY, Properties JSON );

INSERT INTO Employees (EmployeeID, FullName,  Role, Department)  
VALUES    
(1, "Seamus Hogan", "Manager", "Management"),    
(2, "Thomas Eriksson", "Assistant ", "Sales"),   
(3, "Simon Tolo", "Executive", "Management"),   
(4, "Francesca Soffia", "Assistant  ", "Human Resources"),   
(5, "Emily Sierra", "Accountant", "Finance"),    
(6, "Greta Galkina", "Accountant", "Finance"), 
(7, "Maria Carter", "Executive", "Human Resources"), 
(8, "Rick Griffin", "Manager", "Marketing"); 

INSERT INTO Activity(ActivityID, Properties) VALUES  
(1, '{ "ClientID": "Cl1", "ProductID": "P1", "Order": "True" }' ),  
(2, '{ "ClientID": "Cl2", "ProductID": "P4", "Order": "False" }' ),  
(3, '{ "ClientID": "Cl5", "ProductID": "P5", "Order": "True" }' );

-- 1. Avoiding the use of unnecessary columns in the SELECT clause.
SELECT * FROM Orders;

SELECT OrderID, ProductID, Quantity, Date FROM Orders;

-- 2.  Index columns in a table that are used to regularly filter SELECT queries.
SELECT * FROM Orders WHERE ClientID ='Cl1'; 
EXPLAIN SELECT * FROM Orders WHERE ClientID ='Cl1'; 

CREATE INDEX IdxClientID ON Orders(ClientID);
EXPLAIN SELECT * FROM Orders WHERE ClientID ='Cl1'; 

-- 3. Avoiding the use of leading wildcards in predicates, particularly with the LIKE operator.
SELECT * FROM Employees WHERE FullName LIKE '%Tolo'; 

ALTER TABLE Employees ADD COLUMN ReverseFullName VARCHAR(255); -- Add the ReverseFullName column
UPDATE Employees
SET ReverseFullName = CONCAT(
    SUBSTRING_INDEX(FullName, ' ', -1),  -- Extract last name
    ' ',                                 -- Add a space
    SUBSTRING_INDEX(FullName, ' ', 1)   -- Extract first name
);
CREATE INDEX IdxReverseFullName ON Employees (ReverseFullName); -- Index
SELECT * FROM Employees WHERE ReverseFullName LIKE 'Tolo%';

START TRANSACTION; -- BEGIN or BEGIN WORK also works
SQL statements 
ROLLBACK; -- Or COMMIT;

SELECT CONCAT("Cl1: ", COUNT(OrderID), "orders") AS "Total number of orders" FROM Orders WHERE YEAR(Date) = 2022 AND ClientID = "Cl1" 
UNION SELECT CONCAT("Cl2: ", COUNT(OrderID), "orders") FROM Orders WHERE YEAR(Date) = 2022 AND ClientID = "Cl2" 
UNION SELECT CONCAT("Cl3: ", COUNT(OrderID), "orders") FROM Orders WHERE YEAR(Date) = 2022 AND ClientID = "Cl3";

WITH 
CL1_Orders AS (SELECT CONCAT("Cl1: ", COUNT(OrderID), "orders") AS "Total number of orders"  
FROM Orders WHERE YEAR(Date) = 2022 AND ClientID = "Cl1"), 
CL2_Orders AS (SELECT  CONCAT("Cl2: ", COUNT(OrderID), "orders") 
FROM Orders WHERE YEAR(Date) = 2022 AND ClientID = "Cl2"), 
CL3_Orders AS (SELECT  CONCAT("Cl3: ", COUNT(OrderID), "orders") 
FROM Orders WHERE YEAR(Date) = 2022 AND ClientID = "Cl3") 
SELECT * FROM CL1_Orders
UNION 
SELECT * FROM CL2_Orders
UNION 
SELECT * FROM CL3_Orders; 

PREPARE GetOrderDetail FROM 'SELECT OrderID, Quantity, Cost, Date FROM Orders WHERE ClientID = ? AND YEAR(Date) = ? ';

SET @ID = 'Cl1';
SET @Year = 2020;
EXECUTE GetOrderDetail USING @ID, @Year;

SELECT * FROM Activity;

SELECT Activity.Properties ->>'$.ProductID' 
AS ProductID, Products.ProductName, Products.BuyPrice, Products.SellPrice 
FROM Products INNER JOIN Activity 
ON Products.ProductID = Activity.Properties ->>'$.ProductID' 
WHERE Activity.Properties ->>'$.Order' = "True";