PHP —

Altering Tables and Indexes

Indexes can be added or removed from a table after creation. For example, to add an index to the customer table, you can issue the following statement:

ALTER TABLE customer ADD INDEX cities (city);

To remove an index from the customer table, use the following statement:

ALTER TABLE customer DROP INDEX names;

The ALTER TABLE statement can also be used to add, remove, and alter all other aspects of the table, such as attributes and the primary index. We don't discuss statements for altering the database in this tutorial; many examples can be found in Section 7.8 of the manual.html file that is distributed with MySQL.

Displaying Database Structure with SHOW

Details of databases, tables, and indexes can be displayed with the SHOW command. The SHOW command isn't part of the SQL standard and is MySQL-specific. It can be used in several ways:

SHOW DATABASES

Lists the databases that are accessible by the MySQL DBMS.

SHOW TABLES

Shows the tables in the database once a database has been selected with the use command.

SHOW COLUMNS FROM tablename

Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults, and other information for a table. For example:

SHOW COLUMNS FROM customer

shows the attribute information for the customer table. DESCRIBE table produces the same output.

SHOW INDEX FROM tablename

Presents the details of all indexes on the table, including the PRIMARY KEY. For example:

SHOW INDEX FROM customer

shows that there are two indexes, the primary index and the names index.

SHOW STATUS

Reports details of the MySQL DBMS performance and statistics.