Set 3-2 Flashcards
(31 cards)
Select a storage engine for MySQL.
Physical Design
Specify a column is UNIQUE.
Logical design
Specify an index is CLUSTERED.
Physical design
Determine foreign keys.
Logical design
CREATE INDEX FirstIndex
ON Flight (FlightNumber, AirlineName);
CREATE INDEX SecondIndex
ON Flight (DepartureAirportCode);
The questions below refer to the result table of the following statement:
SHOW INDEX FROM Flight;
How many result table rows have ‘FirstIndex’ in the Key_name column?
2
CREATE INDEX FirstIndex
ON Flight (FlightNumber, AirlineName);
CREATE INDEX SecondIndex
ON Flight (DepartureAirportCode);
The questions below refer to the result table of the following statement:
SHOW INDEX FROM Flight;
FlightNumber is the primary key of Flight. Flight has 90 rows. In the row with Column_name = “FlightNumber”, what is the value of Cardinality?
90
CREATE INDEX FirstIndex
ON Flight (FlightNumber, AirlineName);
CREATE INDEX SecondIndex
ON Flight (DepartureAirportCode);
The questions below refer to the result table of the following statement:
SHOW INDEX FROM Flight;
In the row with Column_name = “AirlineName”, what is the value of Seq_in_index?
2
EXPLAIN
SELECT FlightNumber
FROM Flight
WHERE AirportCode IN
(SELECT AirportCode
FROM Airport
WHERE Country = “Cuba”);
In the row with table = “Airport”, what is the value of select_type?
SUBQUERY
EXPLAIN
SELECT FlightNumber
FROM Flight
WHERE AirportCode IN
(SELECT AirportCode
FROM Airport
WHERE Country = “Cuba”);
In the row with table = “Flight”, the _____ column contains NULL because no indexes exist that might be used to process the query.
key
EXPLAIN
SELECT FlightNumber
FROM Flight
WHERE AirportCode IN
(SELECT AirportCode
FROM Airport
WHERE Country = “Cuba”);
Assume the Airport table contains an AirportCode for Cuba. In the row with table = “Flight”, what is the value of rows?
90
An index never appears in the key column of the EXPLAIN result table for any slow query. Should this index always be dropped?
No
In the EXPLAIN result table for one slow query, the key column is NULL, the filtered column is 1%, and the rows column is 10 million. Should an index be created?
Yes
Additional indexes never slow performance of SELECT queries.
True
In step 1 of physical design for MySQL with InnoDB, the database administrator determines table structures.
True
Which index entry refers to Air China flight 1107?
The sixth entry
Lufthansa flight 44, departing from Munich, is inserted into the table. The code for Munich is MUC. Where does the new index entry go?
Between the LAX and ORD index entries.
An index entry requires 12 bytes. Pointers to table blocks require 8 bytes. Index blocks are 4 kilobytes. Approximately how many rows can be referenced in one index block?
200
An index on a non-unique column can either:
A) store multiple pointers after one value, or
B) store duplicate values with one pointer each.
What is the advantage of strategy A?
Index is more compact.
Refer to the following scenario:
A table occupies 2,000 blocks. FlightNumber is the primary key. An index on FlightNumber occupies 200 blocks. The WHERE clause of a SELECT specifies "FlightNumber = 3988".
The query is executed with an index scan.
What is the maximum number of blocks necessary to process the SELECT?
201
Refer to the following scenario:
A table occupies 2,000 blocks. FlightNumber is the primary key. An index on FlightNumber occupies 200 blocks. The WHERE clause of a SELECT specifies "FlightNumber = 3988".
The query is executed with an index scan.
What is the minimum number of blocks necessary to process the SELECT?
2
Refer to the following scenario:
A table occupies 2,000 blocks. FlightNumber is the primary key. An index on FlightNumber occupies 200 blocks. The WHERE clause of a SELECT specifies "FlightNumber = 3988".
The query is executed with an index scan.
Assume the table has no index. What is the maximum number of blocks necessary to process the SELECT?
2000
Refer to the following scenario:
A table has 100 million rows. Each row is 400 bytes. Each block is 8 kilobytes. Each index entry is 20 bytes. Magnetic disk transfer rate is 0.1 gigabytes per second.
Assuming no free space, a table scan requires approximately how many seconds?
40
Refer to the following scenario:
A table has 100 million rows. Each row is 400 bytes. Each block is 8 kilobytes. Each index entry is 20 bytes. Magnetic disk transfer rate is 0.1 gigabytes per second.
Assuming the index is sorted, a binary search for one row reads approximately how many blocks?
log2 250,000
Index with one entry for each table row.
Dense Index
