Retrieving information about Database, Tables, Columns, and Indexes
...................................................................
You can use `db_name.tbl_name' as an alternative to the `tbl_name FROM
db_name' syntax. These two statements are equivalent:
mysql> SHOW INDEX FROM mytable FROM mydb;
mysql> SHOW INDEX FROM mydb.mytable;
`SHOW DATABASES' lists the databases on the MySQL server host. You can
also get this list using the `mysqlshow' command.
`SHOW TABLES' lists the tables in a given database. You can also get
this list using the `mysqlshow db_name' command.
*NOTE:* If a user doesn't have any privileges for a table, the table
will not show up in the output from `SHOW TABLES' or `mysqlshow
db_name'.
`SHOW OPEN TABLES' lists the tables that are currently open in the
table cache. Note:Table cache. The `Comment' field tells how many
times the table is `cached' and `in_use'.
`SHOW COLUMNS' lists the columns in a given table. If you specify the
`FULL' option, you will also get the privileges you have for each
column. If the column types are different than you expect them to be
based on a `CREATE TABLE' statement, note that MySQL sometimes changes
column types. Note:Silent column changes.
The `DESCRIBE' statement provides information similar to `SHOW COLUMNS'.
Note:`DESCRIBE'.
`SHOW FIELDS' is a synonym for `SHOW COLUMNS', and `SHOW KEYS' is a
synonym for `SHOW INDEX'. You can also list a table's columns or
indexes with `mysqlshow db_name tbl_name' or `mysqlshow -k db_name
tbl_name'.
`SHOW INDEX' returns the index information in a format that closely
resembles the `SQLStatistics' call in ODBC. The following columns are
returned:
*Column* *Meaning*
`Table' Name of the table.
`Non_unique' 0 if the index can't contain duplicates.
`Key_name' Name of the index.
`Seq_in_index' Column sequence number in index,
starting with 1.
`Column_name' Column name.
`Collation' How the column is sorted in the index.
In MySQL, this
can have values
`A' (Ascending) or `NULL' (Not
sorted).
`Cardinality' Number of unique values in the index.
This is updated
by running
`isamchk -a'.
`Sub_part' Number of indexed characters if the
column is only
partly indexed.
`NULL' if the entire key is indexed.
`Comment' Various remarks. For now, it tells
whether index is
FULLTEXT or not.
Note that as the `Cardinality' is counted based on statistics stored as
integers, it's not necessarily accurate for small tables.