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;
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
- SHOW COLUMNS FROM tablename
Shows the attributes, types of attributes, key information, whether
NULLis permitted, defaults, and other information for a table. For example:
SHOW COLUMNS FROM customer
shows the attribute information for the customer table.
tableproduces the same output.
- SHOW INDEX FROM tablename
Presents the details of all indexes on the table, including the
KEY. For example:
SHOW INDEX FROM customer
shows that there are two indexes, the primary index and the
- SHOW STATUS
Reports details of the MySQL DBMS performance and statistics.