Answers to Exercises - MySQL

Answer 1:

A column or index that can contain NULL values cannot be processed as fast as one that cannot contain NULL. i1 and i2 are identical except that i1 cannot contain NULL values, so i1 should be faster to process. Therefore, this query should be faster:

SELECT i1 FROM fastindex WHERE i1 LIKE 'mid%';

Answer 2:

SELECT i1 FROM fastindex WHERE i1 LIKE 'mid%';

would probably perform faster because i1 is indexed with only the first three bytes as subpart of that index. MySQL can look up that index faster because it contains only up to three-character rows, as compared to the second index that could contain up to ten- character rows.

Answer 3:

Insert, delete, and update operations will become slower when the table has indexes, because those operations require the indexes to be updated, too.

Answer 4:

You can use EXPLAIN to determine the number of rows MySQL must inspect to calculate the result sets:

mysql> EXPLAIN SELECT * FROM City WHERE Name

BETWEEN 'E' AND 'G'
-> ORDER BY Name\G
*********************** 1. row ***************************
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4079
Extra: Using where; Using filesort
mysql> EXPLAIN SELECT * FROM City WHERE

CountryCode >= 'Y'
-> ORDER BY Name\G
*********************** 1. row ***************************
table: City
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4079
Extra: Using where; Using filesort

The EXPLAIN output shows that MySQL would not use indexes to process the queries. All rows (4,079) must be scanned to calculate the results. This is indicated by the ALL value in the type column as well.

See section A.1.15, "EXPLAIN."

Answer 5:

To improve performance, indexes should be added to the Name and CountryCode columns because those are the columns used in the comparisons that determine which rows to return. Also, because Name is used in the ORDER BY clause, an index on Name can speed up sorting operations.

For the Name column, the results of the queries in question indicate that an index with a prefix length that is shorter than the full column length is likely to improve performance even more. However, the prefix length should be long enough to differentiate cities that begin with words like "East Lo...", so we choose a prefix length of 10:

mysql> ALTER TABLE City
-> ADD INDEX (Name(10)),
-> ADD INDEX (CountryCode)
-> ;

Answer 6:

Another means of making table lookups faster is to declare the table's columns to be NOT NULL. Assume that City must contain a city name in each row, as well as a country code for each city. To disallow NULL values in the Name and CountryCode columns, you could alter the table with this SQL statement:

mysql> ALTER TABLE City
-> MODIFY Name CHAR(35) NOT NULL,
-> MODIFY CountryCode CHAR(3) NOT NULL
-> ;
Query OK, 4079 rows affected (0.21 sec)
Records: 4079 Duplicates: 0 Warnings: 0
mysql> DESCRIBE City;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  |      | PRI | NULL    | auto_increment |
| Name        | char(35) |      | MUL |         |                |
| CountryCode | char(3)  |      | MUL |         |                |
| District    | char(20) | YES  |     | NULL    |                |
| Population  | int(11)  | YES  |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

See section A.1.1, "ALTER TABLE."

Answer 7:

To check whether MySQL actually uses the new indexes to resolve the queries, use EXPLAIN once again:

mysql> EXPLAIN SELECT * FROM City WHERE Name

BETWEEN 'E' AND 'G'
-> ORDER BY Name\G
*********************** 1. row ***************************
table: City
type: range
possible_keys: Name
key: Name
key_len: 5
ref: NULL
rows: 146
Extra: Using where; Using filesort
mysql> EXPLAIN SELECT * FROM City WHERE CountryCode

>= 'Y' ORDER BY Name\G
*********************** 1. row ***************************
table: City
type: range
possible_keys: CountryCode
key: CountryCode
key_len: 3
ref: NULL
rows: 76
Extra: Using where; Using filesort

The EXPLAIN output shows that the indexes you would expect to be used actually are used by MySQL to resolve the queries. Compared to the previous results from EXPLAIN (three questions previously), the number of rows inspected drops dramatically from 4,079 to 146 and 76 due to the use of indexes.

See section A.1.15, "EXPLAIN."

Answer 8:

Table enumtest has a primary key on its only column col. Therefore, there can be only unique values in that column. Because of the ENUM column type, this means that there can be only four different values in the column (the three enumeration members and the empty string that is used for invalid values). false is an invalid value, so it is converted to '' (the empty string). The last value (fourth) is not in the ENUM list, either, so it too is converted to the error value ''. The primary key, however, prevents that same value from being stored again, which leads to a duplicate key error:

mysql> INSERT INTO enumtest VALUES
-> ('first'),('second'),('third'),('false'),

('fourth');
ERROR 1062: Duplicate entry '' for key 1

For a multiple-row INSERT statement, rows are inserted as long as no error occurs. If a row fails, that row and any following rows are not inserted. As a result, the table contents are:

mysql> SELECT * FROM enumtest;
+--------+
| col    |
+--------+
|        |
| first  |
| second |
| third  |
+--------+
4 rows in set

See section A.3, "Column Types."

Answer 9:

A search for 'MySQL' in the question column only could be performed as follows:

mysql> SELECT
-> LEFT(question,20), LEFT(answer,20)
-> FROM faq
-> WHERE MATCH(question) AGAINST('MySQL')
-> ;

The result of the query could look like this:

+------------------------+-----------------------+
| LEFT(question,20)      | LEFT(answer,20)       |
+------------------------+-----------------------+
| Does MySQL support t   | Yes, as of version 3  |
| When will MySQL supp   | This is on the TODO   |
| Does MySQL support f   | Yes, as of version 3  |
| Does MySQL support s   | Not yet, but stored   |
| Is MySQL available u   | Yes, you can buy a l  |
| When was MySQL relea   | MySQL was first rele  |
+------------------------+-----------------------+

See sections A.1.39, "SHOW INDEX" and A.1.11, "DESCRIBE."

Answer 10:

A search for 'Access' in either the question or the answer column could be performed as follows:

mysql> SELECT
-> LEFT(question,20), LEFT(answer,20)
-> FROM faq
-> WHERE MATCH(question) AGAINST('Access')
-> OR MATCH(answer) AGAINST('Access')
-> ;

The result of the query could look like this:

+----------------------+-----------------------+
| LEFT(question,20)    | LEFT(answer,20)       |
+----------------------+-----------------------+
| Is there a database  | Access will most pro  |
| Is Microsoft Access  | It's sold as a datab  |
+----------------------+-----------------------+

Note that OR in the preceding query means that you're looking for the word "Access" whether it appears only in the question, only in the answer, or in both the question and the answer. To find records that contain "Access" in both the question and the answer, you would use AND instead of OR in the query.

See sections A.1.39, "SHOW INDEX," and A.1.11, "DESCRIBE."