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

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,575
    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. Come play with me!
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,754
    Rep Power
    9397
    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
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,575
    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. Come play with me!
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,754
    Rep Power
    9397
    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
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,575
    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. Come play with me!
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,754
    Rep Power
    9397
    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,027
    Rep Power
    4210
    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
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  14. #8
  15. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,575
    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,027
    Rep Power
    4210
    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 07:14 PM.
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  18. #10
  19. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,575
    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