does a primary key speed up an index?

Question

Aside from the convenient auto-increment and UNIQUE features, does the PK actually speed up the index?

Will the speed be the same whether it's a non-PKed indexed INT or PKed (same column, two different tests)? If I had the same column on the same table on the same system, will it be faster if a UNIQUE INT column with an index also has PK enabled? Does PK make the index it coexists with faster?

Please, actual results only with system stats if you could be so kind.

Solution 1

The primary key for a table represents the column or set of columns that you use in your most vital queries. It has an associated index, for fast query performance. Query performance benefits from the NOT NULL optimization, because it cannot include any NULL values. With the InnoDB storage engine, the table data is physically organized to do ultra-fast lookups and sorts based on the primary key column or columns.

If your table is big and important, but does not have an obvious column or set of columns to use as a primary key, you might create a separate column with auto-increment values to use as the primary key. These unique IDs can serve as pointers to corresponding rows in other tables when you join tables using foreign keys.

Also refer the following locations : http://www.dbasquare.com/2012/04/04/how-important-a-primary-key-can-be-for-mysql-performance/ and http://www.w3schools.com/sql/sql_primarykey.asp

Solution 2

Rows in a base table are uniquely identified by the value of the primary key defined for the table. The primary key for a table is composed of the values of one or more columns.

Primary keys are automatically indexed to facilitate effective information retrieval.

The primary key index is the most effective access path for the table.

Other columns or combinations of columns may be defined as a secondary index to improve performance in data retrieval. Secondary indexes are defined on a table after it has been created (using the CREATE INDEX statement).

An example of when a secondary index may be useful is when a search is regularly performed on a non-keyed column in a table with many rows, defining an index on the column may speed up the search. The search result is not affected by the index but the speed of the search is optimized.

It should be noted, however, that indexes create an overhead for update, delete and insert operations because the index must also be updated.

Indexes are internal structures which cannot be explicitly accessed by the user once created. An index will be used if the internal query optimization process determines it will improve the efficiency of a search.

SQL queries are automatically optimized when they are internally prepared for execution. The optimization process determines the most effective way to execute each query, which may or may not involve using an applicable index.