Professional LAMP: Linux®, Apache, MySQL®, and PHP5 Web Development

4.6. Analyzing the Database

While your time using a database will most likely be spent adding, updating, retrieving, and deleting data, there will be occasions when you need to immediately find out information about the database structure itself. Thankfully, MySQL provides some easy-to-use informational tools, as well as simple ways to analyze and optimize your databases.

When inside the MySQL command shell, you can use several different commands that share the prefix SHOW, to get various bits of information about the current state of the database. The following sections describe some of the more common SHOW commands.

4.6.1. SHOW COLUMNS

The SHOW COLUMNS command returns a listing of all the columns in a table, and their attributes. The general format of SHOW COLUMNS is as follows:

SHOW COLUMNS FROM [table] FROM [database]

If you want to get a list of all the columns in the New_Vehicle table, you could use the following:

SHOW COLUMNS FROM New_Vehicles FROM VehicleInventory;

Which returns the following:

+-------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+----------------+ | vehicle_id | int(11) | | PRI | NULL | auto_increment | | model_id | int(11) | YES | | NULL | | | price | decimal(10,2) | YES | | NULL | | | color | varchar(200) | YES | | NULL | | | description | text | YES | | NULL | | | modelyear | int(11) | YES | | NULL | | +-------------+---------------+------+-----+---------+----------------+ ...