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

Answers to 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

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."



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