Optimize the query in the select script using alter table

Assignment Help Database Management System

Reference no: EM13166207

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;

Reference no: EM13166207

Questions Cloud

Which pair is written with first member having the higher : Which pair is written with the first member having the higher boiling point?

Draw a lewis structure for h2nnh2 : Draw a Lewis Structure for C3O2. Draw a Lewis Structure for C2H3NO5

State what concentration of ca remains in solution : If 2.55 g of NaOH were added to a 100.0 mL solution containing 0.10 M Ca(NO3)2, what concentration of Ca 2+ remains in solution?

The grid is populated randomly : Initially, the grid is populated randomly with occupied and empty cells. Once the initial grid has been created, the program loops. Each iteration of the loop represents a tick or time step in the environment.

Optimize the query in the select script using alter table : 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.

What is the approximate ph after adding the sodium acetate : If you are given sodium acetate and asked to make a .5 M acetate buffer at pH 4.75, will you have to add acid, base, or nothing in order to achieve the proper pH? What is the approximate pH after adding the sodium acetate?

Consider the standard heats of formation : Consider the following standard heats of formation: Standard Heat of formations for the substances:CO2(g) = -393.5 kJ/mol PbO(s) = -151.6 kJ/mol CO(g)

Before each sort, write psudo-code : Ceate a single cpp ?le (FILE=MAIN2.cpp) containing all three elementry sorts(bubble, insertion, selection). Before each sort, write psudo-code and invarient analysis of the sort in block comment style. Add the code from 3.). Generate a list of 100..

How much potassium nitrate will perciapitate : A supersaturated solution of potassium nitrate is formed by adding 150 g KNO3 to 100 g water, heating until the solute completely dissolves is agitated, how much potassium nitrate will perciapitate.

User Account

All Pages