dbrowse

Keys and Indexing

This node is informative and descriptive.
[definitions]

Query Path

Interactive Path

Synopsis

This node lists a summary of all the keys from the active table in the current database.

Node/View

[skip to the dbrowse Navigation information]

SQL Statements

The following two MySQL statements are synonymous:

Again, I prefer the first form for its compact syntax.

Description of Results

Relational database architecture and indexing in particular are complex topics. I can only hope to give you a brief glimpse at what indexing may look like for a few small tables in these databases. Improving your skill and knowledge will involve lots of study, practice and experimentation. My best advice is to keep your fields as small as possible and index those you search for, not the data you extract. Whenever possible, use small unsigned integer auto_increment types (they must be indexed) as primary keys—your lookups will be lighting fast if you do.

A good place to start on this topic is with the MySQL Reference Manual section on Table and Index Structures. For other documentation regarding the information displayed on this page, visit the sections on Indexes and how MySQL Uses Indexes. Short descriptions are listed below, some of which have links to even more specific sections of the manual.

Please! Do NOT be a be confused by terminology, a “key” and an “index” are the same thing. Actually, to be more accurate, a key is the field or column that is being indexed, and the index is the database system (usually an ordered structure of pointers stored in a file) used to locate records in the table data.

MySQL Terms

Field

The field, or column name the key is indexing.

Key

The key identifier. Although you can name any index you create, if you create a Primary key without one, MySQL will give it that name for you. Other key types must be named either when you create the table, or if you use the Alter statement to add one later.

Dupe

If the index can contain duplicate values (non-uniques).

Seq

The sequence number for this column in the table’s index. Starting with 1.

Uniq

The cardinality, or the number of unique values in the index. A higher cardinality improves the chances of MySQL using the index when doing table joins. See also Analyze Table.

Prefix

The number of characters or bytes (depending on the character set) into the column that are indexed. If you only need to match the first few characters on long string types this will save you a large amount of space on disk for the index file. Or All for the entire field.

Type

The index type. Depending on the table type, the column type, and the index method used, this may be BTREE, FULLTEXT, HASH, or RTREE. See Storage Engines for more information.

Navigation

To view the details of one index in this table, select the key from the left-hand Field column. To continue browsing this database, use your Web browser’s navigation features such as the back and forward buttons, the Backspace key, or the query and interactive interfaces provided with this application.