Optimizing the Logical Database Structure - 13.5 Exercises (Page 3 of 6 )
Question 1:
Consider the following table with two indexes:
mysql> DESCRIBE fastindex;
+-------+----------+------+-----+
| Field | Type | Null | Key |
+-------+----------+------+-----+
| i1 | char(10) | | MUL |
| i2 | char(10) | YES | MUL |
+-------+----------+------+-----+
With no other facts given, which of the following queries would you expect to run faster?
SELECT i1 FROM fastindex WHERE i1 LIKE 'mid%';
SELECT i2 FROM fastindex WHERE i2 LIKE 'mid%';
Question 2:
Consider the following table with indexes:
mysql> SHOW CREATE TABLE fastindex;
+-----------+---------------------------
| Table | Create Table
+-----------+---------------------------
| fastindex | CREATE TABLE ´fastindex´ (
´i1´ char(10) NOT NULL default '',
´i2´ char(10) NOT NULL default '',
KEY ´i1´ (´i1´(3)),
KEY ´i2´ (´i2´)
) TYPE=MyISAM |
+-----------+---------------------------
With no other facts given, which of the following queries would you expect to run faster?
SELECT i1 FROM fastindex WHERE i1 LIKE 'mid%';
SELECT i2 FROM fastindex WHERE i2 LIKE 'mid%';
Question 3:
For what reason can adding indexes to a table make table operations slower?
Question 4:
Consider the following table structure, which will be used for the next four questions:
mysql> DESCRIBE City;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | | PRI | NULL | auto_increment |
| Name | char(35) | YES | | NULL | |
| CountryCode | char(3) | YES | | NULL | |
| District | char(20) | YES | | NULL | |
| Population | int(11) | YES | | 0 | |
+-------------+----------+------+-----+---------+----------------+
You frequently retrieve data from the City table, using queries similar to those shown here:
mysql> SELECT * FROM City WHERE Name BETWEEN 'E'
AND 'G' ORDER BY Name;
+------+------------------+-------------+--------------+------------+
| ID | Name | CountryCode | District | Population |
+------+------------------+-------------+--------------+------------+
| 735 | East London | ZAF | Eastern Cape | 221047 |
| 3963 | East Los Angeles | USA | California | 126379 |
| 1845 | East York | CAN | Ontario | 114034 |
| 533 | Eastbourne | GBR | England | 90000 |
| 1720 | Ebetsu | JPN | Hokkaido | 118805 |
| ... | ... | ... | ... | ... |
mysql> SELECT * FROM City WHERE CountryCode >= 'Y'
ORDER BY name;
+------+------------+-------------+----------------+------------+
| ID | Name | CountryCode | District | Population |
+------+------------+-------------+----------------+------------+
| 1781 | Aden | YEM | Aden | 398300 |
| 1784 | al-Mukalla | YEM | Hadramawt | 122400 |
| 721 | Alberton | ZAF | Gauteng | 410102 |
| 724 | Benoni | ZAF | Gauteng | 365467 |
| 1792 | Beograd | YUG | Central Serbia | 1204000 |
| ... | ... | ... | ... | ... |
How would you determine the number of rows MySQL must inspect to calculate the result sets?
Question 5:
Consider, once again, the table structure and sample queries shown for the City table in the previous question. What index or indexes would you add to the table to speed up the queries?
Question 6:
Here again are the table structure and sample queries first shown for the City table two questions previously, but with the addition of the indexes on the Name and CountryCode columns from the previous question:
mysql> DESCRIBE City;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | | PRI | NULL | auto_increment |
| Name | char(35) | YES | MUL | NULL | |
| CountryCode | char(3) | YES | MUL | NULL | |
| District | char(20) | YES | | NULL | |
| Population | int(11) | YES | | 0 | |
+-------------+----------+------+-----+---------+----------------+
mysql> SELECT * FROM City WHERE Name BETWEEN 'E'
AND 'G' ORDER BY Name;
+------+------------------+-------------+--------------+------------+
| ID | Name | CountryCode | District | Population |
+------+------------------+-------------+--------------+------------+
| 735 | East London | ZAF | Eastern Cape | 221047 |
| 3963 | East Los Angeles | USA | California | 126379 |
| 1845 | East York | CAN | Ontario | 114034 |
| 533 | Eastbourne | GBR | England | 90000 |
| 1720 | Ebetsu | JPN | Hokkaido | 118805 |
| ... | ... | ... | ... | ... |
mysql> SELECT * FROM City WHERE CountryCode >= 'Y'
ORDER BY name;
+------+------------+-------------+----------------+------------+
| ID | Name | CountryCode | District | Population |
+------+------------+-------------+----------------+------------+
| 1781 | Aden | YEM | Aden | 398300 |
| 1784 | al-Mukalla | YEM | Hadramawt | 122400 |
| 721 | Alberton | ZAF | Gauteng | 410102 |
| 724 | Benoni | ZAF | Gauteng | 365467 |
| 1792 | Beograd | YUG | Central Serbia | 1204000 |
| ... | ... | ... | ... | ... |
In addition to adding indexes to the City table, what else can be done, with regard to the table's columns, to improve performance?
Question 7:
Consider, once again, the new table structure and the sample queries shown for the City table in the previous question. How would you find out whether the new indexes on the table are actually used to resolve the queries?
Question 8:
Consider the following table:
mysql> DESCRIBE enumtest;
+-------+--------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------------+------+-----+---------+-------+
| col | enum('first','second','third') | | PRI | first | |
+-------+--------------------------------+------+-----+---------+-------+
mysql> SELECT * FROM enumtest;
Empty set
Will the following statement fail or will it insert rows? What will the contents of the enumtest table be after executing the statement?
mysql> INSERT INTO enumtest VALUES
-> ('first'),('second'),('third'),('false'),
('fourth');
Question 9:
Consider the following table, which has two single-column FULLTEXT indexes:
mysql> DESCRIBE faq;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| cdate | timestamp(14) | YES | | NULL | |
| question | char(150) | | MUL | | |
| answer | char(250) | | MUL | | |
+----------+---------------+------+-----+---------+-------+
mysql> SHOW INDEX FROM faq;
+-------+------------+----------+- -+-------------+- -+------------+-
| Table | Non_unique | Key_name | ...| Column_name | ...| Index_type | ...
+-------+------------+----------+- -+-------------+- -+------------+-
| faq | 1 | question | ...| question | ...| FULLTEXT | ...
| faq | 1 | answer | ...| answer | ...| FULLTEXT | ...
+-------+------------+----------+- -+-------------+- -+------------+-
With MATCH ... AGAINST(), you can search the answers and the questions stored in the table. How would you search for a search term 'MySQL' in the question column?
Question 10:
Consider the following table, which has two single-column FULLTEXT indexes:
mysql> DESCRIBE faq;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| cdate | timestamp(14) | YES | | NULL | |
| question | char(150) | | MUL | | |
| answer | char(250) | | MUL | | |
+----------+---------------+------+-----+---------+-------+
mysql> SHOW INDEX FROM faq;
+-------+------------+----------+- -+-------------+- -+------------+-
| Table | Non_unique | Key_name | ...| Column_name | ...| Index_type | ...
+-------+------------+----------+- -+-------------+- -+------------+-
| faq | 1 | question | ...| question | ...| FULLTEXT | ...
| faq | 1 | answer | ...| answer | ...| FULLTEXT | ...
+-------+------------+----------+- -+-------------+- -+------------+-
With MATCH ... AGAINST(), you can search the answers and the questions stored in the table. How would you search for the search term 'Access' in either the question or the answer column?
Next: More Exercises >>
More MySQL Articles
More By Sams Publishing
|
 This article is excerpted from chapter 13 of the MySQL Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 14 
