optimize the query in the select script using Alter Table to add appropriate indexes. Also use Optimize and Explain to show how the performance of this query has improved.
How can we Keep Optimizing??
CREATE TABLE Cities
(
CityID SMALLINT NOT NULL PRIMARY KEY,
CityName VARCHAR(20) NOT NULL
)
ENGINE=INNODB;
INSERT INTO Cities
VALUES (101, 'Seattle'), (102, 'Redmond'), (103, 'Bellevue'),
(104, 'Kent'), (105, 'Kirkland');
CREATE TABLE Customers
(
CustID SMALLINT NOT NULL PRIMARY KEY,
CustFN VARCHAR(20) NOT NULL,
CustMN VARCHAR(20) NULL,
CustLN VARCHAR(20) NOT NULL
)
ENGINE=INNODB;
INSERT INTO Customers (CustID, CustFN, CustMN, CustLN)
VALUES (1,'Ralph', 'Frederick', 'Johnson'),
(2,'Hubert', 'T.', 'Weatherby'),
(3,'Anne', NULL, 'Thomas'),
(4,'Mona', 'J.', 'Cavenaugh'),
(5,'Peter', NULL, 'Taylor'),
(6,'Ginger', 'Meagan', 'Delaney');
CREATE TABLE CustCity
(
CustID SMALLINT NOT NULL,
CityID SMALLINT NOT NULL
)
ENGINE=INNODB;
INSERT INTO CustCity
VALUES (1, 104), (2, 101), (3, 104),
(4, 103), (5, 102), (6, 105);
SELECT CustLN, CityName
FROM Customers AS cu, CustCity AS cc, Cities AS ci
WHERE cu.CustID=cc.CustID AND ci.CityID=cc.CityID
AND CityName = 'Kent' ;
--------------------------------------
--------------------------------------
EXPLAIN SELECT CustLN, CityName
FROM Customers AS cu, CustCity AS cc, Cities AS ci
WHERE cu.CustID=cc.CustID AND ci.CityID=cc.CityID;
ALTER TABLE CustCity ADD PRIMARY KEY (CustID, CityID),
ADD FOREIGN KEY (CustID) REFERENCES Customers (CustID),
ADD FOREIGN KEY (CityID) REFERENCES Cities (CityID);
OPTIMIZE TABLE CustCity;
EXPLAIN SELECT CustLN, CityName
FROM Customers AS cu, CustCity AS cc, Cities AS ci
WHERE cu.CustID=cc.CustID AND ci.CityID=cc.CityID;