SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    primary keys and an index

    Hello MySQL gurus

    Assuming I create the following table:

    CREATE TABLE table (
    ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    Column1 INT UNSIGNED NOT NULL,
    Column2 VARCHAR(255) NOT NULL,
    PRIMARY KEY (ID)
    ) ENGINE=MYISAM DEFAULT CHARSET=latin1;

    Now ID is the primary key of the table which means it has a unique index on it (right?)

    will adding the following have any additional effect?
    CREATE index_name ON table(ID);

    Or is such an index automatically created for all the primary keys in a table?

    Also, putting storage issues aside, what would be the disadvantages to creating an index for every column that I use in a join, (in order to speed things up)?

    thanks for the help

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by davidklonski
    Now ID is the primary key of the table which means it has a unique index on it (right?)
    that's correct, and as you can imagine, forcing uniqueness for a number that the database assigns sequentially might seem wasteful (it is), but the index is the important part of an auto_increment being a primary key
    will adding the following have any additional effect?
    CREATE index_name ON table(ID);
    i'm not exactly sure, but i'm guessing the database will go ahead and create it (rather than tellin you "dude, what's the point, you already have an index on that column")
    Or is such an index automatically created for all the primary keys in a table?
    yes, except that there can only be one primary key
    Also, putting storage issues aside, what would be the disadvantages to creating an index for every column that I use in a join, (in order to speed things up)?
    the disadvantage is that while indexes speed up SELECTs, they slow down UPDATEs, DELETEs, and INSERTs
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •