Optimizing the Logical Database Structure - More answers (Page 6 of 6 )
Answer 11:
EXPLAIN provides the following information:
For table City, EXPLAIN indicates that all table rows must be scanned to find the desired information (ALL). There are no keys on the columns that should be retrieved, nor on the column mentioned in the ORDER BY clause, so no keys are used as indicated by the NULL entries for possible_keys, key, key_len, and ref. Therefore, all 4,079 table rows are scanned. Using filesort indicates that MySQL needs to do an extra pass to find out how to retrieve the rows in sorted order.
For table Country, EXPLAIN shows a join type of eq_ref. This is the best possible join type; it means that only one row is read from this table for each row from the previous table. This join type is possible because the index used for table Country is a primary key, as indicated by the PRIMARY entries for possible_keys and key. The primary key has the same length as the column itself (3 bytes, as indicated by key_len, too). ref shows which column is used with the key to select rows from the table: the CountryCode column of the City table. The rows entry of 1 thus indicates that MySQL must examine one row of the Country table to find the match for each CountryCode value selected from the City table.
See section A.1.15, "EXPLAIN."
Answer 12:
To optimize the query shown by the EXPLAIN in the last question, you could create an index for the Population column of the City table because it is used both in the WHERE clause to determine which rows to retrieve and in the ORDER BY clause, to sort the result:
mysql> ALTER TABLE City
-> ADD INDEX (Population)
-> ;
Query OK, 4079 rows affected (0.68 sec)
Records: 4079 Duplicates: 0 Warnings: 0
With the new index, EXPLAIN displays the following for the same 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: range
possible_keys: Population
key: Population
key_len: 4
ref: NULL
rows: 9
Extra: Using where
*********************** 2. row ***************************
table: Country
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: City.CountryCode
rows: 1
Extra:
The EXPLAIN output for the Country table is unchanged, but the output for the City table indicates a much improved search. It shows that only rows within a given range of Population values will be retrieved (type: range), using an index to select the rows. The possible key Population is actually used with its full key length (4). Due to the use of the new index, MySQL now has to inspect only nine rows to resolve the WHERE clause.
See section A.1.15, "EXPLAIN."
Answer 13:
As a rough measure of performance, take the product of the rows output of the EXPLAIN statements before and after the addition of the index: In the original, unoptimized situation, the product of the rows values is 4,079 * 1 = 4,079. With the index added to optimize the query, the product is only 9 * 1 = 9. This lower value indicates that performance is better with the new index.
Answer 14:
To find out which indexes the optimizer will use, prefix your query with EXPLAIN. For example:
EXPLAIN SELECT Name FROM City;
See section A.1.15, "EXPLAIN."
Answer 15:
To rewrite a query that forces MySQL not to use a specific index that the optimizer would otherwise choose, you would use the IGNORE INDEX (or IGNORE KEY) option. For example:
SELECT Name FROM City IGNORE INDEX (idx_name);
See section A.1.29, "SELECT."
Answer 16:
To force the optimizer to use a specific index, you would use the FORCE INDEX (or FORCE KEY) option. For example:
SELECT Name FROM City FORCE INDEX (idx_name);
Another option is USE INDEX, (or USE KEY) but this provides only a hint whereas FORCE INDEX requires the index to be used.
See section A.1.29, "SELECT."
Answer 17:
To find out which query runs faster, you could look at the query execution times the server reports to the client (for example, mysql). These values could, however, be affected by other circumstances than the actual server execution time. More reliable values could be retrieved with the query analyzer (EXPLAIN). This would show that the MySQL optimizer can use indexes more efficiently for the second query:
mysql> EXPLAIN SELECT * FROM key1 WHERE col LIKE
'%2%'\G
************************ 1. row ***************************
table: key1
type: index
possible_keys: NULL
key: col
key_len: 11
ref: NULL
rows: 3599
Extra: Using where; Using index
1 row in set (0.05 sec)
mysql> EXPLAIN SELECT * FROM key1 WHERE col LIKE
'hey2%'\G
************************ 1. row ***************************
table: key1
type: range
possible_keys: col
key: col
key_len: 11
ref: NULL
rows: 1
Extra: Using where; Using index
1 row in set (0.27 sec)
The listing shows—besides other things—that MySQL will have to examine 3,783 rows for the first query, but only 221 for the second one. This occurs because MySQL can use an index for a LIKE pattern match if the pattern begins with a literal value, but not if it begins with a wildcard character.
See section A.1.15, "EXPLAIN."
Answer 18:
To give read requests higher priority than write requests, you can use either of the following strategies:
See section A.1.18, "INSERT."
Answer 19:
To improve searches on the id and name columns, you essentially have two choices:
You could add an index to the name column, thus improving searches for names.
You could split the table into two separate tables, thus avoiding disk I/O caused by the TEXT column when MySQL has to scan the table. The mix1 table could be split as shown here:
mysql> DESCRIBE mix1; DESCRIBE mix2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | | PRI | 0 | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
+---------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| mix1_id | int(11) | | | 0 | |
| story | text | YES | | NULL | |
+---------+---------+------+-----+---------+-------+
You could also combine both of the strategies just described.
See sections A.1.11, "DESCRIBE," and A.1.39, "SHOW INDEX."
Answer 20:
To improve searches on the story column, you could add a FULLTEXT index to that column, like this:
mysql> ALTER TABLE mix1 ADD FULLTEXT (story);
mysql> SHOW KEYS FROM mix1;
+-------+------------+----------+- -+------------+
| Table | Non_unique | Key_name | ... | Index_type |
+-------+------------+----------+- -+------------+
| mix1 | 0 | PRIMARY | ... | BTREE |
| mix1 | 1 | story | ... | FULLTEXT |
+-------+------------+----------+- -+------------+
See sections A.1.11, "DESCRIBE," A.1.39, "SHOW INDEX," and A.1.1, "ALTER TABLE."
Answer 21:
In that scenario, the only solution would be to use MERGE tables, and to split up the MyISAM tables into a number of smaller MyISAM tables, each of which will not hit the filesystem size limit.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter 13 of the MySQL Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 14 
