Index Reports and Utilities

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

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

Table 7.1. SHOW INDEX Output

Column

Purpose

Table

The name of the table being reviewed.

Non_unique

1 if the index can hold duplicates, 0 if not.

Key_name

The name assigned to the index.

Seq_in_index

The position of the column in the index if this is a multicolumn index. The first column will be set to 1.

Column_name

The name of the column.

Collation

The method of index sorting. For now, it can only be 'A' (ascending) or NULL. In future releases, it might also indicate descending indexes.

Cardinality

A count of the unique values for the index. A low number in comparison to the number of rows means that this is a highly duplicate index; a high number means that this index is more unique. Higher values translate into more likely selection by the optimizer when building a query plan.

Sub_part

The value that shows the number of characters that have been placed into the index, if the column is only partially indexed.

Packed

A description of how the index is packed. If not packed, NULL will be displayed.

Null

Columns that may potentially hold NULL values are indicated with a YES.

Index_type

The type of index (RTREE, FULLTEXT, HASH, BTREE).

Comment

A placeholder that contains information from earlier versions that are now given their own column(s).


How can you interpret the results of SHOW INDEX for the sample table? A few facts jump out:

  • The primary key has all unique values because its cardinality is equal to the number of rows in the table. Of course, this is expected from a primary key.

  • Columns six and seven are also completely unique. Why is that? As it turns out, col6 is defined as DATETIME, so the values that the random data loader inserted happened to be completely unique. Col7 is a BLOB, and it too was given completely unique values by the random data creator.

  • Col4 has a very low cardinality (five) in contrast to the number of rows in the table (1.25 million). Why is this? Recall that this column was defined as an ENUM, with only five potential values: Mercury, Venus, Earth, Mars, and Jupiter. This translates to a cardinality of five.

  • Our multicolumn index (col3, col2), although containing two relatively nonunique columns, is itself unique. If you multiply the cardinality of these two columns (2,158 x 2,000), you receive a result much higher than the number of rows in this table.

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 Indexes

Chapter 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 Logging

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


    MySQL Database Design and Tuning
    MySQL Database Design and Tuning
    ISBN: 0672327651
    EAN: 2147483647
    Year: 2005
    Pages: 131

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net