CIS275 - Chapter 5: Data Storage Flashcards
the time required to access the first byte in a read or write operation.
Access time
the speed at which data is read or written, following initial access.
Transfer rate
memory that is lost when disconnected from power.
Volatile memory
memory that is retained without power.
Non-volatile memory
the primary memory used when computer programs execute.
Main memory,
also called random-access memory (RAM),
Main memory is fast, expensive, and has limited capacity.
_____ is less expensive and higher capacity than main memory.
Flash memory,
also called solid-state drive (SSD)
Writes to flash memory are much slower than reads, and both are much slower than main memory writes and reads.
Memory used to store large amounts of data.
Magnetic disk,
also called hard-disk drive (HDD)
Magnetic disk is slower, less expensive, and higher capacity than flash memory.
Magnetic disk groups data in _____.
sectors
traditionally 512 bytes per sector but 4 kilobytes with newer disk formats.
Flash memory groups data in _____
pages
usually between 2 kilobytes and 16 kilobytes per page.
Databases and file systems use a uniform size, called a _____, when transferring data between main memory and storage media.
block
Block size is independent of storage media.
Database systems typically support block sizes ranging from 2 kilobytes to 64 kilobytes. Smaller block sizes are usually better for transactional applications, which access a few rows per query. Larger block sizes are usually better for analytic applications, which access many rows per query.
To minimize block transfers, relational databases usually store an entire row within one block, which is called _____.
row-oriented storage
Row-oriented storage performs best when row size is small relative to block size, for two reasons:
Improved query performance. When row size is small relative to block size, each block contains many rows. Queries that read and write multiple rows transfer fewer blocks, resulting in better performance.
Less wasted storage. Row-oriented storage wastes a few bytes per block, since rows do not usually fit evenly into the available space. The wasted space is less than the row size. If row size is small relative to block size, this wasted space is insignificant.
- The row size is small relative to the block size, so many rows are transferred per block.
- Only 16 bytes go unused, so wasted space is insignificant.
- The row size is large relative to block size, so fewer rows are transferred per block.
- One kilobyte goes unused, so wasted space is significant.
- The large column allows only two rows to be transferred per block and wastes significant space.
- Large columns are replaced by a link and are stored in a separate area.
- More rows fit per block, and less space is wasted. Queries that do not access the large column are faster.
In column-oriented storage, also called columnar storage, each block stores values for a single column only.
column-oriented storage,
also called columnar storage
Column-oriented storage benefits analytic applications in several ways:
Faster data access. More column values are transferred per block, reducing time to access storage media.
Better data compression. Databases often apply data compression algorithms when storing data. Data compression is usually more effective when all values have the same data type. As a result, more values are stored per block, which reduces storage and access time.
- With column-oriented storage, a 4 kilobyte block contains roughly 500 8-byte column values.
- Computing the average of all incomes reads fewer blocks than row-oriented storage.
- Different columns occupy separate blocks.
- Selecting multiple columns reads multiple blocks and is slower than row-oriented storage.
a scheme for organizing rows in blocks on storage media.
table structure
Databases commonly support four alternative table structures:
Heap table
Sorted table
Hash table
Table cluster
In a _____, no order is imposed on rows.
heap table
- In a heap table, the database maintains a pointer to free space, which indicates the location of the next insert.
- When a row is inserted, the pointer moves to the next available space.
- When a row is deleted, the pointer moves to the deleted space. The deleted space is linked to free space at the end of the table.
- As more rows are deleted, free space is linked together in a list.
- Inserts go to the first available space in the list.
In a _____, the database designer identifies a _____ that determines physical row order.
sorted table
sort column
- In a sorted table, rows are sorted on a sort column.
- Inserting a new row requires moving all subsequent rows, which is inefficient.
- To avoid inefficient inserts, the sort column order is maintained with links, producing a linked list.
- The sort order is maintained during an insert by changing two links rather than moving rows.
In a _____, rows are assigned to buckets.
hash table
A _____ is a block or group of blocks containing rows.
bucket
Initially, each bucket has one block. As a table grows, some buckets eventually fill up with rows, and the database allocates additional blocks. New blocks are linked to the initial block, and the bucket becomes a chain of linked blocks.
The bucket containing each row is determined by a hash function and a hash key.
The _____ is a column or group of columns, usually the primary key.
hash key
The _____ computes the bucket containing the row from the hash key.
hash function
Hash functions are designed to scramble row locations and evenly distribute rows across blocks. The modulo function is a simple hash function with four steps:
Convert the hash key by interpreting the key’s bits as an integer value.
Divide the integer by the number of buckets.
Interpret the division remainder as the bucket number.
Convert the bucket number to the physical address of the block containing the row.
- FlightNumber is the hash key for the Flight table.
- The hash function is modulo 10, resulting in 10 buckets. Rows are assigned to buckets based on the last digit of the hash key.
- If a bucket fills with rows, additional blocks are allocated and linked to the first block.
A _____ automatically allocates more blocks to the table, creates additional buckets, and distributes rows across all buckets.
dynamic hash function
Expected:
free space A
free space B → free space A
In a heap table, the database maintains a pointer to the first free space in the table. In the above table, free space A is the only free space, so the pointer points to free space A.
Row 659 is deleted, so the free space pointer moves to free space B. Free space B is linked to free space A, the next free space.
Expected:
free space B,
free space A,
free space A,
new block
When row 1961 is deleted, the free space pointer moves to free space B. Free space B is linked to free space A, the next free space.
Because the free space pointer now points to free space B, the first insert is stored in free space B. Then, the free space pointer is reset to point to free space A, so the second insert is stored in free space A. There is still room for another row in free space A, so the third insert is also stored in free space A. Since the block is now full, the free space pointer is set to point to the beginning of the new block, where the fourth insert is stored.
Expected:
23 → 24, 24 → 27
No sort columns
A pointer links a row to the row with the next higher key. Since 23 < 24 < 27, pointers 23 → 24 and 24 → 27 are added to table A.
No columns in table B are sorted, so table B has no sort columns.
Expected:
14 → 20, 20 → 21
Name, State, (Name, State)
A pointer links a row to the row with the next higher key. Since 14 < 20 < 21, pointers 14 → 20 and 20 → 21 are added to table A.
In table B, the Name and State columns are sorted, so either column is a valid sort column.
The sort column can be a group of columns. The Name and State columns are sorted, so the Name and State columns together constitute a valid sort column, regardless of which column contains the primary key values.
Expected:
22 → 24, 24 → 25
No sort columns
A pointer links a row to the row with the next higher key. Since 22 < 24 < 25, pointers 22 → 24 and 24 → 25 are added to table A.
No columns in table B are sorted, so table B has no sort columns.
Consider a hash table with 5 buckets and a modulo function. Which bucket does each hash key map to?
Expected:
0, 3, 1, 4, 4
5 buckets exist, so the hash function is modulo 5.
755 modulo 5 is 0, so bucket 0.
1098 modulo 5 is 3, so bucket 3.
446 modulo 5 is 1, so bucket 1.
1204 modulo 5 is 4, so bucket 4.
1499 modulo 5 is 4, so bucket 4.
Consider a hash table with 5 buckets and a modulo function. Which bucket does each hash key map to?
Expected:
0, 2, 1, 4, 2
5 buckets exist, so the hash function is modulo 5.
1425 modulo 5 is 0, so bucket 0.
302 modulo 5 is 2, so bucket 2.
1321 modulo 5 is 1, so bucket 1.
914 modulo 5 is 4, so bucket 4.
177 modulo 5 is 2, so bucket 2.
Consider a hash table with 5 buckets and a modulo function. Which bucket does each hash key map to?
Expected: 4, 2, 0, 3, 1
5 buckets exist, so the hash function is modulo 5.
184 modulo 5 is 4, so bucket 4.
582 modulo 5 is 2, so bucket 2.
355 modulo 5 is 0, so bucket 0.
433 modulo 5 is 3, so bucket 3.
256 modulo 5 is 1, so bucket 1.
Expected: 231 blocks, 640 rows
(5,600 bytes/block) / (70 bytes/row) = 80 rows/block
So 18,465 rows would need:
18,465 rows / (80 rows/block) = 230 blocks with remainder 65, so 231 blocks.
Each bucket has 1 block, and 1 block has 80 rows. So 8 buckets can have up to 80 × 8 = 640 rows.
Expected: 421 blocks, 630 rows
(6,300 bytes/block) / (70 bytes/row) = 90 rows/block
So 37,825 rows would need:
37,825 rows / (90 rows/block) = 420 blocks with remainder 25, so 421 blocks.
Each bucket has 1 block, and 1 block has 90 rows. So 7 buckets can have up to 90 × 7 = 630 rows.
_____, interleave rows of two or more tables in the same storage area.
Table clusters, also called multi-tables
Table clusters are optimal when joining interleaved tables on the cluster key, since physical row location is the same as output order. Table clusters perform poorly for many other queries:
Join on columns other than cluster key. In a join on a column that is not the cluster key, physical row location is not the same as output order, so the join is slow.
Read multiple rows of a single table. Table clusters spread each table across more blocks than other structures. Queries that read multiple rows may access more blocks.
Update clustering key. Rows may move to different blocks when the clustering key changes.
Table clusters are not optimal for many queries and therefore are not commonly used.
- Airline and Flight tables both contain an AirlineName column.
- AirlineName is the cluster key for the table cluster.
- Rows of Airline and Flight are interleaved on storage media.
Table clusters have a _____, a column that is available in all interleaved tables.
cluster key
The cluster key determines the order in which rows are interleaved. Rows with the same cluster key value are stored together. Usually the cluster key is the primary key of one table and the corresponding foreign key of another, as in the animation below.
a file containing column values, along with pointers to rows containing the column value.
single-level index
- An index is on the DepartureAirport column of the Flight table.
- Each column value appears in the index in sorted order.
- Each column value has a pointer to the row containing the value.
- If the column values are not unique, the index may have multiple entries for some values.
- Alternatively, an index on a non-unique column may have multiple pointers for some values.
In a _____, each index entry is a composite of values from all indexed columns.
multi-column index
In all other respects, multi-column indexes behave exactly like indexes on a single column.
- FlightNumber is the Flight table’s primary key. FlightNumber is sorted but is not indexed.
- The SELECT statement has no WHERE clause, so the hit ratio is high. The database performs a table scan and reads all table blocks.
- The WHERE clause does not contain an indexed column, so hit ratio is high. The database must perform a table scan.
- Only the first two blocks are read because FlightNumber 803 is found in the second table block.
- Since the hit ratio is low and the WHERE clause contains an indexed column, the database performs an index scan.
a database operation that reads table blocks directly, without accessing an index.
table scan
a database operation that reads index blocks sequentially, in order to locate the needed table blocks.
index scan
the percentage of table rows selected by a query.
Hit ratio, also called filter factor or selectivity
When a SELECT query is executed, the database examines the WHERE clause and estimates hit ratio. If hit ratio is high, the database performs a table scan. If hit ratio is low, the query needs only a few table blocks, so a table scan would be inefficient. Instead, the database:
Looks for an indexed column in the WHERE clause.
Scans the index.
Finds values that match the WHERE clause.
Reads the corresponding table blocks.
If the WHERE clause does not contain an indexed column, the database must perform a table scan.
In a _____, the database repeatedly splits the index in two until it finds the entry containing the search value:
binary search
- The database first compares the search value to an entry in the middle of the index.
- If the search value is less than the entry value, the search value is in the first half of the index. If not, the search value is in the second half.
- The database now compares the search value to the entry in the middle of the selected half, to narrow the search to one quarter of the index.
- The database continues in this manner until it finds the index block containing the search value.
- The DepartureAirport index occupies nine blocks. The SELECT query looks for the row containing DepartureAirport = ‘DUB’.
- A binary search first examines the middle, block 4, of the sorted index.
- Since ‘DUB’ precedes ‘ORD’, the binary search resets last to block 3 and middle to block 1.
- Since ‘DUB’ follows ‘BWI’, search resets first and middle to block 2.
- The binary search reads block 2 and finds ‘DUB’. The database follows DUB’s pointer to the table block containing the row.
A _____ contains an entry for every table block.
sparse index
The table is not sorted on DepartureAirport column. The index on DepartureAirport must be dense.
The table is sorted on DepartureAirport column.
The index on the sort column may be sparse.
‘LAX’ does not appear in the index, but the block containing ‘LAX’ can be determined from sort order.
- A new row is inserted into the table with a dense sorted index. A new index entry ‘PIT’ must be inserted in sort order.
- Since the index block is full, the block splits. Half of the existing entries are moved to the new block.
- The block split creates space for the new index entry.
Expected:
(k)
Index entry (k) contains the value SEA with a pointer to the row that refers to Delta Airlines flight 1222.
Expected:
between the HKG and LAX entries
Since the single-level index is sorted, an entry for JFK goes between the HKG and LAX entries. The database must create space for the new entry.
Expected:
between the LAX and ORD entries
Since the single-level index is sorted, an entry for NRT goes between the LAX and ORD entries. The database must create space for the new entry.
Expected:
251, 9000
The database reads at most 250 index blocks, plus one table block containing the row for flight 1695. With no index, the database performs a table scan and reads at most all 9000 table blocks.
Expected:
201, 5000
The database reads at most 200 index blocks, plus one table block containing the row for flight 1062. With no index, the database performs a table scan and reads at most all 5000 table blocks.
Expected:
225 seconds, 4500000 blocks
(900,000,000 rows) × (500 bytes/row) × (1 gigabyte/1,000,000,000 bytes) / (2 gigabytes/sec) = 225 seconds
Each index block contains 8 kb × (1000 bytes/kb) / 40 bytes/entry = 200 entries. The index has (900,000,000 entries) / (200 entries/block) = 4,500,000 blocks. A binary search requires log(2)4,500,000 blocks.
Expected:
640 seconds, 20000000 blocks
(800,000,000 rows) × (400 bytes/row) × (1 gigabyte/1,000,000,000 bytes) / (0.5 gigabytes/sec) = 640 seconds
Each index block contains 4 kb × (1000 bytes/kb) / 100 bytes/entry = 40 entries. The index has (800,000,000 entries) / (40 entries/block) = 20,000,000 blocks. A binary search requires log(2) 20,000,000 blocks.
Expected: 2000 seconds, 5000000 blocks
(400,000,000 rows) × (500 bytes/row) × (1 gigabyte/1,000,000,000 bytes) / (0.1 gigabytes/sec) = 2000 seconds
Each index block contains 8 kb × (1000 bytes/kb) / 100 bytes/entry = 80 entries. The index has (400,000,000 entries) / (80 entries/block) = 5,000,000 blocks. A binary search requires log(2) 5,000,000 blocks.
Expected: 300 seconds, 375000 blocks
(600,000,000 rows) × (500 bytes/row) × (1 gigabyte/1,000,000,000 bytes) / (1 gigabyte/sec) = 300 seconds
Each index block contains 16 kb × (1000 bytes/kb) / 10 bytes/entry = 1600 entries. The index has (600,000,000 entries) / (1600 entries/block) = 375,000 blocks. A binary search requires log(2) 375,000 blocks.
A _____ stores column values and row pointers in a hierarchy.
multi-level index
The bottom level of the hierarchy is a sorted single-level index. The bottom level is sparse for primary and clustering indexes, or dense for secondary indexes.
- Flight table is sorted on non-unique column DepartureAirport.
- The bottom level is a sparse index on DepartureAirport. If the table is not sorted on the index column, the bottom level must be dense.
- The next higher level is a sparse index to the lower level.
The number of index entries per block is called the _____ of a multi-level index.
fan-out
The number of levels in a multi-level index can be computed from fan-out, number of rows, and rows per block:
For a dense index, number of levels = logfan-out (number of rows)
For a sparse index, number of levels = logfan-out (number of rows / rows per block)
- A query searches for rows containing ‘FRA’.
- The database reads the index’s top level. ‘FRA’ is between ‘ATL’ and ‘MIN’.
- The database follows the ‘ATL’ pointer and reads the bottom-level block. ‘FRA’ is between ‘FLL’ and ‘GRB’.
- The database follows the ‘FLL’ pointer, reads the table block, and locates the row containing ‘FRA’.
Multi-level indexes are called _____ when all branches are the same length.
balanced
Imbalanced indexes are undesirable, since processing time is unpredictable. If a query follows a long branch, the query is relatively slow. For this reason, inserts are managed to maintain balanced indexes:
- In a dense index, inserts always generate new bottom-level index entries. In a sparse index, inserts generate new bottom-level index entries when table blocks split.
- If the new index entry goes in a full index block, the block splits. Half of the rows move to the new block, creating space for the entry.
- The new block in the bottom level generates a new index entry the next level up. If block in the next level up is full, the block splits and the process repeats.
- If blocks are full at all index levels, the split propagates to the top level. In this case, the top-level block splits and a new level is created.
- The Flight table is sorted on DepartureAirport. A row is inserted with DepartureAirport ‘FSK’.
- The table block is full and splits to create space for the new row.
- The new table block generates a new entry in the index’s bottom level.
- The bottom-level index block is full and splits.
- The new bottom-level block generates a new entry for the index’s top level.
- The top-level index is full and splits. A new top level is created containing entries for two blocks in the middle level.
If an indexed value appears in a higher level, the value is not repeated at lower levels. Instead, a pointer to the corresponding table block appears in the higher level along with the value.
B-tree
In a B-tree, inserts, updates, and deletes may cause a table pointer to change levels, which is hard to implement. B+trees do not have this problem, since table pointers are always in the bottom level.
- The bottom level of a B-tree is like the bottom level of a B+tree.
- Higher levels of a B-tree alternate index block pointers and table block pointers.
- Entries that appear in higher levels are not repeated in the bottom level.
In a _____, index entries are assigned to buckets.
hash index
A hash index is similar to a hash table, described in another section. However, a hash index stores index entries in each bucket, while a hash table stores table rows in each bucket.
Sometimes the term hash index is used to mean a hash key, but the two terms are different. A hash index is an index that is structured using a hash function. A hash key is a column that determines the physical location of rows in a hash table.
- The table is clustered on DepartureAirportCode.
- The hash index is on FlightNumber. The hash function is modulo 10.
- Index entries for flight numbers ending in 0 go in bucket 0.
- Index entries for flight numbers ending in 1 go in bucket 1.
- Since the hash function is modulo 10, the hash index has 10 buckets.
A _____ is a grid of bits:
bitmap index
- Each index row corresponds to a unique table row. If the table’s primary key is an integer, the index row number might be the primary key value. Alternatively, the index row number might be an internal table row number, maintained by the database.
- Each index column corresponds to a distinct table value. Ex: If the index is on AirportCode, each index column corresponds to a different three-letter airport code. The mapping of index column numbers to table values is computed with a function or stored in an internal ‘lookup’ table.
- The database maintains row numbers internally.
- A bitmap index is a grid of bits. Bitmap index rows correspond to internal table row numbers.
- Bitmap index columns correspond to table values.
- Table row 0 contains ATL, so the corresponding index bit is 1.
- Table row 1 contains DUB, so the corresponding index bit is 1.
- Each 1 in the bitmap index indicates the corresponding value appears in the table row.
- Each 0 in the bitmap index indicates the corresponding value does not appear in the table row.
A _____ is a single- or multi-level index in which pointers to table blocks are replaced with primary key values.
logical index
Logical indexes are always secondary indexes and require a separate primary index on the same table.
To locate a row containing a column value, the database:
- Looks up the column value in the logical index to find the primary key value.
- Looks up the primary key value in the primary index to find the table block pointer.
- Reads the table block containing the row.
- The table is sorted on FlightNumber, the primary key. This example assumes FlightNumber is unique across all airlines.
- The table has primary index on FlightNumber. The primary index is physical and sparse.
- The table has secondary index on DepartureAirportCode. The secondary index is logical and dense.
- To find the row containing EUG, the database finds the primary key 200 in the secondary index, then finds the block pointer in the primary index leading to the block containing 200.
In a _____, the database designer specifies a function on the column value.
function index
Index entries contain the result of the function applied to column values, rather than the column values.
Ex: Column values are stored as decimal numbers between 0 and 1, but users specify percentages as integers between 0 and 100 in queries. The database designer specifies a function index that multiplies column values by 100 and converts the result to an integer. The index contains integers between 0 and 100, so the database can use the function index to process queries.
- The table contains times in AM/PM format.
- Queries specify time in 24-hour format.
- The function index converts AM/PM format to 24-hour format.
- The function index format is consistent with the WHERE clause, so the database can use the index.
Expected:
Flight numbers 735, 905, 255, 885, and 995 The hash function is modulo 10, so index entries for flight numbers ending in 5 go in bucket 5. 735, 905, 255, 885, and 995 are the flight numbers that end in 5.
5, 8
1, 3, 11
6:00 and 7:35
a database object that maps one or more tables to a single file.
tablespace
The CREATE TABLESPACE statement names a tablespace and assigns the tablespace to a file. The CREATE TABLE statement assigns a table to a tablespace. Indexes are stored in the same tablespace as the indexed table.
- CodeTablespace contains small, read-only code tables and is stored on a flash drive for fast reads.
- The State table is assigned to CodeTablespace.
- Additional tables are assigned to the same tablespace to reduce overhead.
- The Employee table is partitioned on hire date.
- Each partition corresponds to one year.
- Each partition is stored in a separate tablespace.
A _____ associates each partition with a range of partition expression values.
range partition
The VALUES LESS THAN keywords specify the upper bound of each range. The MAXVALUE keyword represents the highest column value, and VALUES LESS THAN MAXVALUE specifies the highest range. Each partition is explicitly named by the database administrator.
- Order table has columns for order number, date, product code, and amount of order.
- Range partition assigns partitions based on OrderDate year.
- Rows are assigned to one of the three partitions based on year.
A _____ associates each partition with an explicit list of partition expression values using the VALUES IN keywords.
list partition
Like a range partition, each partition is explicitly named.
- Order table has columns for order number, date, product code, and amount of order.
- List partition assigns partitions based on specific ProductCodes.
- Each partition contains rows with specific product codes.
A _____ requires a partition expression with positive integer values.
hash partition
The database administrator specifies the number of partitions, N, and partitions are automatically named p0 through p(N-1). The partition number for each row is computed as: (partition expression value) modulo N.
A _____ is similar to a hash partition, except the partition expression is determined automatically by the database.
key partition
- Order table has columns for order number, date, product code, and amount of order.
- Range partition assigns partitions based on OrderDate year.
- Rows are assigned to one of the three partitions based on year.
- List partition assigns partitions based on specific ProductCodes.
- Each partition contains rows with specific product codes.
- Hash partition expression is HASH(OrderNumber) with ten partitions.
- Each row is assigned to the partition OrderNumber modulo 10, so assigned partitions are based on the order number’s last digit.
_____ specifies tables, columns, and keys.
Logical design
- Logical design specifies tables and columns.
- Logical design also specifies primary and foreign keys.
______ specifies indexes, table structures, and partitions.
Physical design
Physical design affects query performance but never affects query results.
- Physical design specifies table structure. Flight table is sorted on DepartureAirportCode.
- Physical design also specifies indexes and index types.
The _____ statement creates an index by specifying the index name and table columns that compose the index.
CREATE INDEX
Most indexes specify just one column, but a composite index specifies multiple columns.
The ______ statement deletes a table’s index.
DROP INDEX
The _____ statement displays a table’s index.
SHOW INDEX
SHOW INDEX generates a result table with one row for each column of each index. A multi-column index has multiple rows in the result table.
- The Address table has primary key AddressID and foreign key CityID.
- The Address table has primary key AddressID and foreign key CityID.
- The CREATE INDEX statement creates a secondary index on PostalCode.
- The SHOW INDEX statement displays all indexes on the Address table. Some columns of the result table have been omitted for clarity.
- MySQL created indexes on the primary and foreign keys automatically when Address was created. AddressID has 603 distinct index values, and CityID has 99 distinct index values.
- PostalCodeIndex was created with the CREATE INDEX statement. PostalCodeIndex is an index on a non-unique column with 597 distinct values.
The _____ generates a result table that describes how a statement is executed by the storage engine.
EXPLAIN statement
EXPLAIN syntax is simple and uniform in most databases: EXPLAIN statement; The statement can be any SELECT, INSERT, UPDATE, or DELETE statement.
Although EXPLAIN syntax is standard, the result table varies significantly by database. In MySQL with InnoDB, the result table has one row for each table in the statement. If the statement contains multiple queries, such as a main query and a subquery, the result table has one row for each table in each query.
- Address table has 603 rows. City table has 57 rows.
- The SELECT statement joins Address and City tables.
- EXPLAIN generates a results table with one row for each table in the SELECT statement.
- The Address table is accessed via table scan. Of 603 rows read, 33% are selected.
- The City table is accessed via primary index. One City row is read and selected for each Address row.
- The SELECT statement appears in the MySQL slow query log.
- The database administrator runs EXPLAIN on the SELECT query.
- The EXPLAIN result shows no index was used, many rows were read, and few rows were selected.
- To speed up the SELECT query, the database administrator creates an index on the CityName column.
- Running EXPLAIN again shows the query uses CityNameIndex, reads only one row, and executes faster.
