#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,688
    Rep Power
    171

    Phpmyadmin not showing indexes


    This is very strange I create a table with the simple query
    Code:
     CREATE TABLE products_and_status
      (
         product_id INT NOT NULL,
         status_id  INT NOT NULL,
         PRIMARY KEY ( product_id, status_id ),
         CONSTRAINT fk_product_id FOREIGN KEY (product_id) REFERENCES products_table
         (id),
         CONSTRAINT fk_status_id FOREIGN KEY (status_id) REFERENCES products_status(
         id)
      )
    engine = innodb
    But:

    1 - PhpMyadmin does not show product_id foreign key in indexes view? It only shows fk_status_id. Why?

    2 - Also SHOW CREATE TABLE strangely shows KEY `fk_status_id` (`status_id`)!

    a) It is a foreign key, why does it show it as KEY?

    b)Why doesn't it show KEY `fk_product_id` (`product_id`)! It has been created exactly as status_id! SHOW CREATE TABLE:
    Code:
    CREATE TABLE `products_and_status`
      (
         `product_id` INT(11) NOT NULL,
         `status_id`  INT(11) NOT NULL,
         PRIMARY KEY (`product_id`, `status_id`),
         KEY `fk_status_id` (`status_id`),
         CONSTRAINT `fk_product_id` FOREIGN KEY (`product_id`) REFERENCES
         `products_table` (`id`),
         CONSTRAINT `fk_status_id` FOREIGN KEY (`status_id`) REFERENCES
         `products_status` (`id`)
      )
    engine=innodb
    DEFAULT charset=latin1
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,298
    Rep Power
    9400
    The primary key covers an index on the product_id already so I'd guess MySQL doesn't want to go through the effort of duplicating that.
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,688
    Rep Power
    171
    Originally Posted by requinix
    The primary key covers an index on the product_id already so I'd guess MySQL doesn't want to go through the effort of duplicating that.
    I don't think that's it. Primary key covers an index on product_id and status_id both.
  6. #4
  7. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,298
    Rep Power
    9400
    And product_id. A key over A+B+C+D also functions as a key over A, A+B, and A+B+C.
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,688
    Rep Power
    171
    Originally Posted by requinix
    And product_id. A key over A+B+C+D also functions as a key over A, A+B, and A+B+C.
    I understand the "Index has to be leftmost prefixes in order to work" so I get what you say above. But what I am asking about the foreign key. Whats the relation?
  10. #6
  11. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,298
    Rep Power
    9400
    1. Because there is no "fk_product_id" index.
    2a. MySQL requires indexes for foreign key constraints and will create them automatically if you don't do it yourself.
    2b. See #1.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Code:
    CREATE TABLE products_and_status
    ( product_id INT(11) NOT NULL
    , status_id  INT(11) NOT NULL
    , PRIMARY KEY (product_id, status_id)
    , KEY fk_status_id (status_id)
    , CONSTRAINT fk_product_id 
      FOREIGN KEY (product_id) REFERENCES products_table (id)
    , CONSTRAINT fk_status_id 
      FOREIGN KEY (status_id) REFERENCES products_status (id)
    ) ENGINE = innodb 
      DEFAULT CHARSET = latin1
    the key shown above in red should really be replaced with --
    Code:
    KEY status_product (status_id, product_id)
    that way, it becomes a covering index and the database will then never need to access the table at all during SELECTs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,688
    Rep Power
    171
    Originally Posted by r937
    Code:
    CREATE TABLE products_and_status
    ( product_id INT(11) NOT NULL
    , status_id  INT(11) NOT NULL
    , PRIMARY KEY (product_id, status_id)
    , KEY fk_status_id (status_id)
    , CONSTRAINT fk_product_id 
      FOREIGN KEY (product_id) REFERENCES products_table (id)
    , CONSTRAINT fk_status_id 
      FOREIGN KEY (status_id) REFERENCES products_status (id)
    ) ENGINE = innodb 
      DEFAULT CHARSET = latin1
    the key shown above in red should really be replaced with --
    Code:
    KEY status_product (status_id, product_id)
    that way, it becomes a covering index and the database will then never need to access the table at all during SELECTs



    Hi;
    The above is the result of SHOW CRATE TABLE! That is NOT how I created the table. This is how I created the table:
    Code:
    CREATE TABLE products_and_status
      (
         product_id INT NOT NULL,
         status_id  INT NOT NULL,
         PRIMARY KEY ( product_id, status_id ),
         CONSTRAINT fk_product_id FOREIGN KEY (product_id) REFERENCES products_table
         (id),
         CONSTRAINT fk_status_id FOREIGN KEY (status_id) REFERENCES products_status(
         id)
      )
    engine = innodb
    And I simply expected SHOW CREATE TABLE to show:
    Code:
    CREATE TABLE `products_and_status`
      (
         `product_id` INT(11) NOT NULL,
         `status_id`  INT(11) NOT NULL,
         PRIMARY KEY (`product_id`, `status_id`),
         CONSTRAINT `fk_product_id` FOREIGN KEY (`product_id`) REFERENCES
         `products_table` (`id`),
         CONSTRAINT `fk_status_id` FOREIGN KEY (`status_id`) REFERENCES
         `products_status` (`id`)
      )
    engine=innodb
    DEFAULT charset=latin1
    I dont understand why KEY fk_status_id (status_id) is even there in SHOW CREATE TABLE and now you are saying it should be replaced with KEY status_product (status_id, product_id) which makes it even more confusing. I ask again why not simply
    Code:
    CREATE TABLE `products_and_status`
      (
         `product_id` INT(11) NOT NULL,
         `status_id`  INT(11) NOT NULL,
         PRIMARY KEY (`product_id`, `status_id`),
         CONSTRAINT `fk_product_id` FOREIGN KEY (`product_id`) REFERENCES
         `products_table` (`id`),
         CONSTRAINT `fk_status_id` FOREIGN KEY (`status_id`) REFERENCES
         `products_status` (`id`)
      )
    engine=innodb
    DEFAULT charset=latin1
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by zxcvbnm
    I dont understand why KEY fk_status_id (status_id) is even there in SHOW CREATE TABLE
    it was created for you by mysql

    see post #6, answer 2a

    i am suggesting that instead of letting mysql create this index for you, you create the 2-column index yourself

    please note, INDEX is the same as KEY... a PRIMARY KEY is a KEY (i.e. INDEX), but a FOREGN KEY is ~not~ a KEY (i.e. INDEX), so one needs to be created

    unfortunate terminology, and mysql should never have allowed KEY to mean INDEX
    Last edited by r937; May 5th, 2013 at 08:14 PM.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,688
    Rep Power
    171
    Thank you for the answers. Somethings telling me I got it right this time

    It looks like everytime there is a foreign key created, an index must be created for that as well! I was highly under the impression that foreign key is an index!!!!!!!

    As primary key can be used as the key (index) for fk_product_id, then there is no need to create an index for fk_product_id anymore.

    But as fk_status_id does not have an index created for it, we can create either KEY status_product (status_id, product_id) or KEY status_product (status_id) as it's index.

    KEY status_product (status_id, product_id) is prefered as it improves the performance more than the other option.

    Code:
    CREATE TABLE products_and_status
      (
         product_id INT NOT NULL,
         status_id  INT NOT NULL,
         PRIMARY KEY ( product_id, status_id ),
         KEY status_product (status_id, product_id),
         CONSTRAINT fk_product_id FOREIGN KEY (product_id) REFERENCES products_table (id),
         CONSTRAINT fk_status_id FOREIGN KEY (status_id) REFERENCES products_status(id)
      )
    engine = innodb

IMN logo majestic logo threadwatch logo seochat tools logo