Sujay Koduri <[EMAIL PROTECTED]> wrote on 11/15/2005 06:47:43 AM: > hi all.. > > i have created a table (INNODB) with a primary key column. > And all the queries i am running are of the following type only. > > select col1,col2... from table where primary_key_col = value > > My question here is will mysql automatically create an index on this column > which is having the primary key constraint. (This is what i am asusming all > the time, as oracle do this) > But when i do a show table status on this table, it is giving the index > length as 0. (does this mean there is no index on this column ?) > > And if i create an index now on this primary key column, is it going to > increase my performance anyway. > > I am using mysql 4.1.14 on RHEL 3.0 > > Thank you > sujay
For InnoDB, the primary key is also a clustering key (the other storage engines do not cluster their data). That means that the data is physically arranged on disk in the order of the PK. No extra space is required for storage so the size of this key is 0. Make sense? And, yes, with MySQL a PK *is* an index (for all storage engines). No need to create a separate key on the same column(s) in an attempt to improve search performance. Just for the sake of completeness: An INDEX is the same thing as a KEY. The two terms are interchangeable. Any KEY will provide a sorted list of the terms used to create the key so that finding instances of those terms in the data will be much faster. A table can contain multiple INDEXes(KEYs). A UNIQUE key is an index with a constraint built into it. It will cause the database engine to reject any record that will duplicate any item already indexed by it. A table can contain multiple UNIQUE indexes A PRIMARY KEY is also an index with a uniqueness constraint (like UNIQUE) and even more. There can only be one PK defined on any table and for the InnoDB engine the PK determines the physical sequencing of data as it is written to disk. The PRIMARY KEY also plays roles in secondary index creation and physical table organization in the other storage engines but not to the extent it does with InnoDB. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
