Tables These are guidelines for the primary key, followed by more detailed explanations. Consider adding a primary key to any table that does not already have one. Use the smallest practical numeric type based on the maximum projected size of the table. This can make each row slightly more compact, which can yield substantial space savings for large tables. The space savings are multiplied if the table has any secondary indexes, because the primary key value is repeated in each secondary index entry. In addition to reducing data size on disk, a small primary key also lets more data fit into the buffer pool, speeding up all kinds of operations and improving concurrency. If the table already has a primary key on some longer column, such as a  VARCHAR , consider adding a new unsigned  AUTO_INCREMENT  column and switching the primary key to that, even if that column is not referenced in queries. This design change can produce substantial space savings in the secondary indexes. You can designate the former primary key columns as  UNIQUE NOT NULL  to enforce the same constraints as the  PRIMARY KEY  clause, that is, to prevent duplicate or null values across all those columns. If you spread related information across multiple tables, typically each table uses the same column for its primary key. For example, a personnel database might have several tables, each with a primary key of employee number. A sales database might have some tables with a primary key of customer number, and other tables with a primary key of order number. Because lookups using the primary key are very fast, you can construct efficient join queries for such tables. If you leave the  PRIMARY KEY  clause out entirely, MySQL creates an invisible one for you. It is a 6-byte value that might be longer than you need, thus wasting space. Because it is hidden, you cannot refer to it in queries. The reliability and scalability features of  InnoDB  require more disk storage than equivalent  MyISAM  tables. You might change the column and index definitions slightly, for better space utilization, reduced I/O and memory consumption when processing result sets, and better query optimization plans making efficient use of index lookups. If you set up a numeric ID column for the primary key, use that value to cross-reference with related values in any other tables, particularly for join queries. For example, rather than accepting a country name as input and doing queries searching for the same name, do one lookup to determine the country ID, then do other queries (or a single join query) to look up relevant information across several tables. Rather than storing a customer or catalog item number as a string of digits, potentially using up several bytes, convert it to a numeric ID for storing and querying. A 4-byte unsigned  INT  column can index over 4 billion items (with the US meaning of billion: 1000 million). For the ranges of the different integer types, see Section 11.1.2, “Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT” . 3033 •Declare a  PRIMARY KEY  for each table. Typically, it is the most important column that you refer to in WHERE  clauses when looking up a single row. •Declare the  PRIMARY KEY  clause in the original  CREATE TABLE  statement, rather than adding it later through an  ALTER TABLE  statement. •Choose the column and its data type carefully. Prefer numeric columns over character or string ones. •Consider using an auto-increment column if there is not another stable, unique, non-null, numeric column to use. •An auto-increment column is also a good choice if there is any doubt whether the value of the primary key column could ever change. Changing the value of a primary key column is an expensive operation, possibly involving rearranging data within the table and within each secondary index. Application Performance Considerations Understanding Files Associated with InnoDB Tables