Critical Skill 3.2 Change a Table s Structure and Add an Index


After you create a table, you may need to make changes to its columns or fields. You might also want to add an index, to speed up table searches.

Altering a Table

Sometimes, you will need to change a table s structure to correct a mistake. Other times, there may be a change in the requirements for a table or its contents. For example, your table might need another field (column) or changes to a field definition.

An example of when you might need to change a table s structure is after you make a new table by creating a copy of an existing table. As you learned in the previous section, if one of the fields copied into the new table was designated the auto-incrementing key, the key designation is lost. In addition, since you cannot have an auto-incrementing field without it also being defined as a key, the AUTO_INCREMENT function is lost. To fix the copied table, you will want to redefine a column as the key. You can make these kinds of changes by using the ALTER TABLE statement.

The ALTER TABLE syntax has four options:

  • ADD The ADD option allows you to add a field to a table s definition. It has two options of its own: FIRST and AFTER . The ADD FIRST option adds a field at the beginning of the table s record definition; the ADD AFTER option allows you to specify which field precedes the added field.

  • MODIFY The MODIFY option changes the format of a field. For instance, you could use this option to change a field from CHAR(5) to DECIMAL(5) or from CHAR(13) to CHAR(42) .

  • CHANGE The CHANGE option renames fields and can be handy for clarifying contents or fixing a typo in the command used to create fields.

  • DROP The DROP option deletes a field, unless INDEX is specified, in which case the field named in the command remains, but it is no longer an index.

The basic variations of the syntax for ALTER TABLE and its options are as follows :

 ALTER TABLE <  table_name  > ADD <  field_name  > <  field type  > <  one or more field options  >, ADD <  field_name  > <  field type  > AFTER <  existing_field_name  >, ADD <  field_name  > <  field type  > FIRST, ADD INDEX <  field_name  >, MODIFY <  field_name  > <  field type  > <  one or more field options  >, CHANGE <  existing_field_name  > <  new_field_name  > <  field type  >, DROP <  field_name  >, DROP INDEX <  field_name  >; 

As you can see, you can specify more than one option in an ALTER TABLE statement. However, if you are making interrelated changes, make sure you list them in a logical order. For instance, if you are making a new field into the index key on a table, make sure you drop the old index first. The following command will alter a table called books , with the columns pub_date , title , surname , given_name , and publisher , by dropping the original index on the field pub_date and adding a new index on the surname field.

 ALTER TABLE books DROP INDEX pub_date, ADD INDEX surname; 

Care must be taken when dropping an index that the reserved word INDEX appears in the command. If the reserved word INDEX had been left out of the previous command, then the column pub_date , along with all of the data stored in it, would be dropped, instead of just the index, leaving the column and its data intact. Always double-check your syntax when using any form of a DROP clause.

The following command will alter the table authors by adding three new columns, changing the name of an existing column to match the last name_first initial naming convention, and modifying the definition of another column from a DATE format ( yyyy/mm/dd ) to a character string 10 spaces long.

 ALTER TABLE authors ADD Herbert_F CHAR(15), ADD Pratchett_T CHAR(15), ADD Gaiman_N CHAR(15), CHANGE L_Hamilton Hamilton_L CHAR(15), MODIFY pub_date CHAR(10); 

Creating an Index

Indexes are created in order to make searching a table faster. It is akin to using an index to find information in a book, rather than looking at every page. The index allows you, figuratively speaking, to jump directly to the page that holds the information you seek.

For all table types except BDB, the index is held in memory. This fact alone speeds access, because the index can be searched without accessing the hard drive. Once the appropriate information has been located in the index, it gives the specific location on the hard drive where the requested data resides, allowing you to go directly there, instead of searching through the entire table.

You might decide to create an index for a table for many reasons. Here are some common indicators of when a table might need an index, which can act as cues.

  • The table contains a large amount of data, requiring time to do an entire search.

  • The data in the table is accessed frequently.

  • The same field usually accesses the data in the table.

    Caution  

    Indexing too much information can negate the advantages of the index. If the table and/or indexed field is large enough, the index itself can become too large. In that event, indexing only a portion of the field, such as the first few characters of a string, can allow you to shrink the size of the index back to the point where it regains the speed advantage.

To give a table an index, use the CREATE INDEX command. It can create an index on one or more fields, but you must remember that the order of the fields is reflected in the order of importance of the indexes. As long as you consider the order you will likely search in, it will be more efficient to have one index that covers fields a , b , and c than to have one a index, one b index, and one c index. If you make an a,b,c index but most of your searches are on the b and c fields, your index will not provide the faster results you were seeking by creating the index in the first place.

The simplest syntax for CREATE INDEX requires a name for the index and then a listing of the table and field or fields that compose the index.

 CREATE INDEX <  index_name  > ON <  table_name  >(<  field_name  >, . . .); 

An expanded syntax specifies a width for the field, which can save space without slowing the search process appreciably.

 CREATE INDEX <  index_name  > ON <  table_name  >(<  field_name  >(  width  ) . . .); 

For instance, most names vary within the first ten characters. If your name field is defined as 25 characters, defining an index as (name(10)) would create a much smaller index than using all of the characters. This index would be only slightly slower to search, and it could even speed up insertions.

The CREATE INDEX command also has two options: FULLTEXT and UNIQUE .

 CREATE FULLTEXT INDEX <  index_name  > ON <  table_name  >(<  field_name  >); CREATE UNIQUE INDEX <  index_name  > ON <  table_name  >(<  field_name  >); 

The FULLTEXT option is allowed only on CHAR , VARCHAR , and TEXT fields that are in MyISAM tables. As the name implies, FULLTEXT specifies an index that compares an entire string to another string. It is used only in conjunction with the MATCH function.

The UNIQUE option is more commonly used than FULLTEXT . It requires any addition to the index column s fields to be unique. If you try to insert or update a duplicate item into a UNIQUE column, an error message will be returned.

The CREATE INDEX command can add a forgotten index, let you set up an index that covers multiple fields, or allow you to redefine a deleted index with new criteria, which takes into account lessons learned by working with your database.

The table music has five columns: artist , title , label , release_date , and tracks_num . It was originally created with an index on the artist column, but you have found that you also need to search using the title column frequently. Because the titles vary in length and are occasionally quite long, the following command creates an index on the title column using the first nine characters, which makes the index smaller to store and should not extend the search time appreciably while possibly speeding insertions.

 CREATE INDEX title_ndx ON music(title(9)); 

If you create a table, sales , and forget to incorporate an index into the definition of the order number, which must be UNIQUE to prevent any billing or shipping duplications, the following command will rectify the omission:

 CREATE UNIQUE INDEX orderIndex ON sales(orderNum); 
Ask the Expert

Where are indexes used in commands?

Indexes are most often used in any command that has a WHERE clause. Indexes allow you to access data more quickly, but they also specify a certain item of data, or if the index is not defined as unique, certain sets of data. Any field of data that is a unique reference for its record is a candidate for indexing, especially if that field is the most likely way a user will identify or search for that record.

Why would you make a multiple-field index?

A multiple-field index is useful when the most common field you search under is likely to have duplicate entries in your database, requiring other fields to be searched in order to narrow the results. The most classic example is a database where you search by name. If the record definition includes last and first names and middle initials , then a three-field index can come in handy.

In a large database of names, such as a telephone directory for a major city, a search for the last name Smith will usually find multiple records. Even if you narrow the search to Smith, George, it is probable that you will find many entries. If the search narrows to Smith, George, J, then the search is more likely to return only a few records that match all three fields. In such a case, an index a, b, c that consists of last, first, and middle initial fields would be ideal.

On the other hand, if you had a database with student, teacher, and school fields, and you searched for data by teacher or school as often as you did by student, then three separate indexes might make more sense. Certainly, a single multiple-field index would be counter-productive, because your search requirements seldom, if ever, require all three categories to narrow your search to the required data. Let your data and your accessing patterns dictate whether you need a single field index, several single-field indexes, or a multiple-field index. Remember, MySQL allows you to create new indexes easily after a table has been created, so it is possible to learn as you go, and create or drop indexes as experience dictates.

 



MySQL(c) Essential Skills
MySQL: Essential Skills
ISBN: 0072255137
EAN: 2147483647
Year: 2006
Pages: 109

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