When you create a table, consider whether it should have indexes, because they have important benefits:
22.2.1. Types of IndexesMySQL supports several types of indexes:
To define indexes when you're initially creating a table, use CREATE TABLE. To add indexes to an already existing table, use ALTER TABLE or CREATE INDEX. Index creation using the INDEX, UNIQUE, and PRIMARY KEY keywords is discussed in Section 8.6, "Indexes." FULLTEXT indexes provide a means for optimizing text searching in MyISAM tables. They are not covered here, but you can read about them in Section 38.3.3, "FULLTEXT Indexes." Spatial indexes are not covered in this study guide or on the exam. 22.2.2. Principles for Index CreationAn index helps MySQL perform retrievals more quickly than if no index is used, but indexes can be used with varying degrees of success. Keep the following index-related considerations in mind when designing tables:
For indexed MyISAM or InnoDB tables, keeping the internal index statistics up to date helps the query optimizer process queries more efficiently. You can update the statistics with the ANALYZE TABLE statement. See Section 38.3.2, "Keep Optimizer Information Up to Date." 22.2.3. Indexing Column PrefixesShort index values can be processed more quickly than long ones. Therefore, when you index a column, ask whether it's sufficient to index partial column values rather than complete values. This technique of indexing a column prefix can be applied to string data types. Suppose that you're considering creating a table using this definition: CREATE TABLE t ( name CHAR(255), INDEX (name) ); If you index all 255 characters of the values in the name column, index processing will be relatively slow:
It's often possible to overcome these problems by indexing only a prefix of the column values. For example, if you expect column values to be distinct most of the time in the first 15 characters, index only that many characters of each value, not all 255 characters. To specify a prefix length for a column, follow the column name in the index definition by a number in parentheses. The following table definition is the same as the previous one, except that key values in the index use only the first 15 characters of the column values: CREATE TABLE t ( name CHAR(255), INDEX (name(15)) ); Indexing a column prefix can speed up query processing, but works best when the prefix values tend to have about the same amount of uniqueness as the original values. Don't use such a short prefix that you produce a very high frequency of duplicate values in the index. It might require some testing to find the optimal balance between long index values that provide good uniqueness versus shorter values that compare more quickly but have more duplicates. To determine the number of records in the table, the number of distinct values in the column, and the number of duplicates, use this query: SELECT COUNT(*) AS 'Total Rows', COUNT(DISTINCT name) AS 'Distinct Values', COUNT(*) - COUNT(DISTINCT name) AS 'Duplicate Values' FROM t; The query gives you an estimate of the amount of uniqueness in the name values. Then run a similar query on the prefix values: SELECT COUNT(DISTINCT LEFT(name,n)) AS 'Distinct Prefix Values', COUNT(*) - COUNT(DISTINCT LEFT(name,n)) AS 'Duplicate Prefix Values' FROM t; That tells you how the uniqueness characteristics change when you use an n-character prefix of the name values. Run the query with different values of n to determine an acceptable prefix length. If an index on a full column is a PRIMARY KEY or UNIQUE index, you'll probably have to change the index to be non-unique if you decide to index prefix values instead. When you index partial column values, it's more likely that the prefix values will contain duplicates. 22.2.4. Leftmost Index PrefixesIn a table that has a composite (multiple-column) index, MySQL can use leftmost index prefixes of that index. A leftmost prefix of a composite index consists of one or more of the initial columns of the index. MySQL's capability to use leftmost index prefixes enables you to avoid creating unnecessary (redundant) indexes. The CountryLanguage table in the world database provides an example of how a leftmost prefix applies. The table has a two-part primary key on the CountryCode and Language columns: mysql> SHOW INDEX FROM CountryLanguage\G *************************** 1. row *************************** Table: CountryLanguage Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: CountryCode Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: CountryLanguage Non_unique: 0 Key_name: PRIMARY Seq_in_index: 2 Column_name: Language Collation: A Cardinality: 984 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: The index on the CountryCode and Language columns allows records to be looked up quickly based on a given country name and language. However, MySQL also can use the index when given only a country code. Suppose that you want to determine which languages are spoken in France: SELECT * FROM CountryLanguage WHERE CountryCode = 'FRA'; MySQL can see that CountryCode is a leftmost prefix of the primary key and use it as though it were a separate index. This means there's no need to define a second index on the CountryCode column alone. On the other hand, if you want to perform indexed searches using just the Language column of the CountryLanguage table, you do need to create a separate index because Language is not a leftmost prefix of the existing index. Note that a leftmost prefix of an index and an index on a column prefix are two different things. A leftmost prefix of an index consists of leading columns in a multiple-column index. An index on a column prefix indexes the leading characters of values in the column. |