More Exercises - MySQL

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?