Home arrow MySQL arrow Page 3 - Optimizing the Logical Database Structure

13.5 Exercises - MySQL

In this final article of our series, you will learn more ways of organizing your data to achieve better query performance. It is excerpted from chapter 13 of the MySQL Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127).

TABLE OF CONTENTS:
  1. Optimizing the Logical Database Structure
  2. 13.4.2 Using Summary Tables
  3. 13.5 Exercises
  4. More Exercises
  5. Answers to Exercises
  6. More answers
By: Sams Publishing
Rating: starstarstarstarstar / 15
August 24, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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?



 
 
>>> More MySQL Articles          >>> More By Sams Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: