1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

MySQL indexes

Discussion in 'MySQL' started by xms, Aug 24, 2017.

  1. #1
    Hello,
    SEMrush
    I have a table:

    CREATE TABLE types
    (
      id                INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
      , info            VARCHAR(128) NOT NULL
      , name            VARCHAR(32) NOT NULL
    
      , PRIMARY KEY (id)
    
      , UNIQUE INDEX (info, name)
    ) ENGINE=InnoDB CHARACTER SET latin1 COLLATE latin1_swedish_ci;
    Code (SQL):
    If I want to have an unique index, should I add the following code?

    CREATE UNIQUE INDEX types_unique_index
        ON types (info, name);
    Code (SQL):
     
    xms, Aug 24, 2017 IP
    SEMrush
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,625
    Likes Received:
    719
    Best Answers:
    151
    Trophy Points:
    420
    #2
    WHY do you need that unique index? You have an auto-increment primary key field - that will be unique, so why do you need the separate, unique index?
     
    PoPSiCLe, Aug 24, 2017 IP
  3. xms

    xms Active Member

    Messages:
    169
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    53
    #3
    This is just an example.
     
    xms, Aug 24, 2017 IP
  4. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,625
    Likes Received:
    719
    Best Answers:
    151
    Trophy Points:
    420
    #4
    Yes, I understand that, but explaining what you want to do, instead of asking "should I do this" usually leads to better answers. Because most times, people asking "should I do this" are on the wrong track to begin with. You should read up on this: What is the XY problem
     
    PoPSiCLe, Aug 24, 2017 IP
  5. xms

    xms Active Member

    Messages:
    169
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    53
    #5
    I just do not know if...

    UNIQUE INDEX (info, name)
    Code (SQL):
    ...does the same job as the following code:

    CREATE UNIQUE INDEX types_unique_index
        ON types (info, name);
    Code (SQL):
     
    xms, Aug 24, 2017 IP
  6. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,625
    Likes Received:
    719
    Best Answers:
    151
    Trophy Points:
    420
    #6
    They are functionally the same, yes, except the latter one has an arbitrary name for the unique multiple index.
     
    PoPSiCLe, Aug 25, 2017 IP