< Day Day Up > |
MySQL offers a collection of helpful reports and utilities that help developers and administrators gain insight into the indexing profile and needs for their own environments. The following sections consider several of these practical tools. SHOW INDEXThe SHOW INDEX command returns a list of valuable data about indexes for a particular table. Because it's vital that you understand the output from this command to help you plan new tables or analyze your existing ones, this section spends some time reviewing how to interpret this information. Before beginning, let's create a sample table containing a variety of data types: CREATE TABLE demo_show_index ( col1 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, col2 VARCHAR(30) NOT NULL, col3 DATE NOT NULL, col4 ENUM ('Mercury', 'Venus', 'Mars', 'Earth', 'Jupiter') NOT NULL, col5 TEXT NOT NULL, col6 DATETIME NOT NULL, col7 BLOB NOT NULL, INDEX (col2), INDEX (col3), INDEX (col4), FULLTEXT (col5), INDEX (col6), INDEX (col7(150)), INDEX (col3,col2) ) ENGINE = MYISAM; An assortment of indexes have been placed on all columns to make the SHOW INDEX output more interesting. Figure 7.5 shows what the table looks like when created and populated. Figure 7.5. A view of the new table's data and index structure from within the MySQL Query Browser.We then loaded 1,250,000 rows of random information into the table, and then ran OPTIMIZE_TABLE to update MySQL's internal statistics: mysql> OPTIMIZE TABLE demo_show_index; +----------------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------------+----------+----------+----------+ | test.demo_show_index | optimize | status | OK | +----------------------+----------+----------+----------+ 1 row in set (45 min 16.09 sec) Notice how long it took to optimize the table; this is not something you would want to run in the middle of your busiest day. For administrators of the MyISAM storage engine, don't forget that you can elect to use the myisamchk command to perform the same table analysis and defragmentation; this command is discussed in Chapter 11, "MyISAM Performance Enhancement." Now, take a look at the output of SHOW INDEX for this table: mysql> SHOW INDEX FROM demo_show_index\ G *************************** 1. row *************************** Table: demo_show_index Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: col1 Collation: A Cardinality: 1250000 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: demo_show_index Non_unique: 1 Key_name: col2 Seq_in_index: 1 Column_name: col2 Collation: A Cardinality: 2000 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 3. row *************************** Table: demo_show_index Non_unique: 1 Key_name: col3 Seq_in_index: 1 Column_name: col3 Collation: A Cardinality: 2158 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 4. row *************************** Table: demo_show_index Non_unique: 1 Key_name: col4 Seq_in_index: 1 Column_name: col4 Collation: A Cardinality: 5 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 5. row *************************** Table: demo_show_index Non_unique: 1 Key_name: col6 Seq_in_index: 1 Column_name: col6 Collation: A Cardinality: 1250000 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 6. row *************************** Table: demo_show_index Non_unique: 1 Key_name: col7 Seq_in_index: 1 Column_name: col7 Collation: A Cardinality: 1250000 Sub_part: 150 Packed: NULL Null: Index_type: BTREE Comment: *************************** 7. row *************************** Table: demo_show_index Non_unique: 1 Key_name: col3_2 Seq_in_index: 1 Column_name: col3 Collation: A Cardinality: 2158 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 8. row *************************** Table: demo_show_index Non_unique: 1 Key_name: col3_2 Seq_in_index: 2 Column_name: col2 Collation: A Cardinality: 1250000 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 9. row *************************** Table: demo_show_index Non_unique: 1 Key_name: col5 Seq_in_index: 1 Column_name: col5 Collation: NULL Cardinality: 144 Sub_part: NULL Packed: NULL Null: Index_type: FULLTEXT Comment: Table 7.1 explains each of the columns in the report.
How can you interpret the results of SHOW INDEX for the sample table? A few facts jump out:
If you've chosen the MyISAM storage engine, you can also use the myisamchk utility to get additional details. This utility is explored in Chapter 11's review of the MyISAM engine. Assisting the Optimizer with IndexesChapter 6, "Understanding the MySQL Optimizer," examines the MySQL optimizer and reviews how this vital technology determines the processing steps that the database engine takes to return the results for a query. Generally, the optimizer makes the correct decision with no intervention from the developer. However, there are some situations, generally driven by a low index cardinality value, in which the optimizer might elect to perform an expensive table scan rather than employing an existing index. For circumstances like this, you can set the max_seeks_for_key parameter (either globally or for one session) to a low value. When this setting is low, the MySQL optimizer believes that the number of key seeks will not exceed this value, despite what its internal index statistics state. This assumption then forces an index lookup, rather than the more expensive table scan. Index-Related LoggingChapter 2, "Performance Monitoring Options," appraises MySQL's logging capa-bilities. You can use these logs to get a better understanding of any problem queries, particularly those that are running without the benefits of indexes. To do so, enable the --log-queries-not-using-indexes server parameter (new as of version 4.1; older versions used --log-long-format instead), and ensure that the --log-slow-queries flag is also enabled. With both these flags set, MySQL logs both slow queries as well as queries that do not make use of an index. For example, look at the following snippet of data from the slow queries log: # Time: 051224 16:52:20 # User@Host: [Ksoze] @ client88 [204.11.13.187] # Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 5000000 select count(*) from customer_address where address1 = '111 Wilson Street'; # Time: 051224 16:52:59 # User@Host: [Bedelman] @ belserv29 [10.82.17.170] # Query_time: 4 Lock_time: 1 Rows_sent: 1 Rows_examined: 390463 select count(*) from customer_address where address1 = '43 Boyd Street'; # Time: 051224 16:55:15 # User@Host: [NSierra] @ mtnview19 [203.19.11.7] # Query_time: 46 Lock_time: 0 Rows_sent: 1 Rows_examined: 21195536 select avg(amount) from transactions where transaction_id between 1 and 4000000; Note that the long_query_time setting specifies the threshold above which any slow-running queries will be logged. For example, if this variable is set to 5 seconds, only those queries that took longer than 5 seconds are logged. The server parameters are discussed in greater detail in Chapters 10, 11, and 12, "General Server Performance Parameters and Tuning," "MyISAM Performance Enhancement," "InnoDB Performance Enhancement," respectively. |
< Day Day Up > |