HomePHP Page 6 - Changing Table Structure in phpMyAdmin
Multi-Field Indexes and Index Editing - PHP
This chapter explores editing table definitions and using special column types. When developing Web applications (or any application), requirements often change because of new or modified needs. Developers must accommodate these changes through judicious table-structure editing. This is chapter six of Mastering phpMyAdmin for Effective MySQL Management by Marc Delisle (Packt Publishing, April 2004, ISBN 1904811035).
In the next example, we will assume that in a future application we will need to find the books written by a specific author in a specific language. It makes sense to expand our author_id index, adding the language field to it.
We click the Edit link (small pencil) on the line containing the author_id index and choose to add one column to this index as shown in the following screenshot:
We select the language field on the next panel. This time we do not have to put a size since the whole field will be used in the index:
For better documentation, we should also change the key name (author_language would be appropriate). We save this index modification and we are back to:
This special type of index allows for full-text searches. It is supported on tables of type MyISAM for VARCHAR and TEXT fields. We can use the Fulltext quick link in the fields list or go to the index management panel and choose Fulltext in the drop-down menu:
We want a FULLTEXT index on the description field so that we are able to locate a book from words present in its description.
After the index creation, the index list looks like:
Seeing 1 as the field length here might be surprising. In fact, MySQL does not support the idea of an index length for FULLTEXT indexes: the index is always on the whole field, but this 1 is the value reported by MySQL.
This chapter is from Mastering phpMyAdmin for Effective MySQL Management by Marc Delisle (Packt Publishing, April 2004, ISBN 1904811035). Check it out at your favorite bookstore today.