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

More 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 11:

Consider the following tables:

mysql> DESCRIBE City; DESCRIBE Country;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| ID          |  int(11) |      |     | 0       |       |
| Name        | char(35) |      |     |         |       |
| CountryCode |  char(3) |      |     |         |       |
| District    | char(20) |      |     |         |       |
| Population  |  int(11) |      |     | 0       |       |
+-------------+----------+------+-----+---------+-------+
+----------------+------------------------+------+-----+---------+-------+
| Field          | Type                   | Null | Key | Default | Extra |
+----------------+------------------------+------+-----+---------+-------+
| Code           | char(3)                |      | PRI |         |       |
| Name           | char(52)               |      |     |         |       |
| Continent      | enum('Asia','Europe',) |      |     | Asia    |       |
| Region         | char(26)               |      |     |         |       |
| SurfaceArea    | float(10,2)            |      |     | 0.00    |       |
| IndepYear      | smallint(6)            | YES  |     | NULL    |       |
| Population     | int(11)                |      |     | 0       |       |
| LifeExpectancy | float(3,1)             | YES  |     | NULL    |       |
| GNP            | float(10,2)            | YES  |     | NULL    |       |
| GNPOld         | float(10,2)            | YES  |     | NULL    |       |
| LocalName      | char(45)               |      |     |         |       |
| GovernmentForm | char(45)               |      |     |         |       |
| HeadOfState    | char(60)               | YES  |     | NULL    |       |
| Capital        | int(11)                | YES  |     | NULL    |       |
| Code2          | char(2)                |      |     |         |       |
+----------------+------------------------+------+-----+---------+-------+

The tables are related: CountryCode in City references Code in Country. What information does the following EXPLAIN statement give you regarding possible optimization of the query?

mysql> EXPLAIN
-> SELECT
-> City.Name, City.Population, Country.Name
-> FROM City INNER JOIN Country
-> ON City.CountryCode = Country.Code
-> WHERE City.Population > 10000000
-> ORDER BY City.Population DESC
-> \G
*********************** 1. row ***************************
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4079
Extra: Using where; Using filesort
*********************** 2. row ***************************
table: Country
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: City.CountryCode
rows: 1
Extra:

Question 12:

Based on the information provided by the EXPLAIN in the previous question, what would you do to optimize the query performance?

Question 13:

Consider, once again, the EXPLAIN output for the Country and City tables from the previous two questions. How would you roughly "measure" the performance for the unoptimized query? For the optimized query?

Question 14:

Most of the time, the MySQL optimizer makes the right choice of indexes to use for a query. However, you suspect that, for a certain query, the optimizer is not making the right choice. How can you determine whether the optimizer is choosing the index you want it to use?

Question 15:

Most of the time, the MySQL optimizer makes the right choice of indexes to use for a query. However, you suspect that, for a certain query, the optimizer is not making the right choice. How could you rewrite the query to determine whether it runs faster without using an index?

Question 16:

Most of the time, the MySQL optimizer makes the right choice of indexes to use for a query. However, you suspect that, for a certain query, the optimizer is not making the right choice. How could you force MySQL to use an index that is different from the index which the optimizer would choose?

Question 17:

Consider the following table and its indexes:

mysql> DESCRIBE key1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| col   | char(10) | YES  | MUL | NULL    |       |
+-------+----------+------+-----+---------+-------+
mysql> SHOW KEYS FROM key1;
+-------+------------+----------+--------------+-------------+-
| Table | Non_unique | Key_name | Seq_in_index | Column_name | ...
+-------+------------+----------+--------------+-------------+-
| key1  |          1 | col      |            1 |     col     | ...
+-------+------------+----------+--------------+-------------+-

Which of the following queries will most likely perform faster, and why? How could you actually find out which query runs faster?

SELECT * FROM key1 WHERE col LIKE '%2%'
SELECT * FROM key1 WHERE col LIKE 'hey 2%'

Question 18:

Assume that you have a table that is subject to many read (SELECT) requests. Compared to the number of reads, you have only a few write (INSERT) requests taking place. Furthermore, you consider the reads more important than the write requests. What could you do to give read requests priority over write requests?

Question 19:

Consider the following table and its indexes:

mysql> DESCRIBE mix1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     |      | PRI | 0       |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| story | text        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
mysql> SHOW KEYS FROM mix1;
+-------+------------+----------+-
| Table | Non_unique | Key_name | ...
+-------+------------+----------+-
| mix1  |          0 | PRIMARY  | ...
+-------+------------+----------+-

Assume that you have many seeks on the mix1 table, most of which use id or name as a search term. Searches are becoming considerably slow. What can you do to improve the situation?

Question 20:

Consider the following table and its indexes:

mysql> DESCRIBE mix1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     |      | PRI | 0       |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| story | text        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
mysql> SHOW KEYS FROM mix1;
+-------+------------+----------+-
| Table | Non_unique | Key_name | ...
+-------+------------+----------+-
| mix1  |     0      | PRIMARY  | ...
+-------+------------+----------+-

Assume that you have many seeks on the mix1 table, most of which look for a search term in the story column. What can you do to speed up those searches?

Question 21:

Assume that you hit a filesystem limit on file size with a MyISAM table. That table contains a FULLTEXT index, so you cannot switch to another storage engine. Also, assume that it isn't possible to change the filesystem you're using. What else could you do to overcome the filesystem size limit?



 
 
>>> 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: