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.

How does INDEX work with MYSQL?

Discussion in 'Databases' started by JEET, Oct 29, 2019.

  1. #1
    How does INDEX work with MYSQL?

    Suppose I got 2 tables like this

    //customerTable
    id auto_increment,
    username char(30),
    password char(40),
    phone int(10)


    //profileTable
    id auto_increment,
    username char(30),
    description text


    And I created an INDEX on username on both tables, like this

    create index username on `customerTable` ( username, password )
    create index username on `profileTable` ( username )
    SEMrush
    Then I run this query:

    Query1:
    select * from `customerTable` where username='abc' limit 1

    Query2:
    select * from `customerTable` where username='abc' and password='xyzzzzz' limit 1

    Query3:
    select customerTable.*, profileTable.* from
    customerTable, profileTable where
    customerTable.username='abc'
    and customerTable.password='xyzzzzzzz'
    and customerTable.username = profileTable.username
    limit 1

    Which indexes will these 3 queries access? Because name of both indexes is same "username"...
    Thanks
     
    Solved! View solution.
    JEET, Oct 29, 2019 IP
    SEMrush
  2. Andrii Ozemko

    Andrii Ozemko Greenhorn

    Messages:
    37
    Likes Received:
    8
    Best Answers:
    1
    Trophy Points:
    23
    #2
    I am not familiar with MySQL, my experience related to MS SQL. So, when you create Index, its name should contain Table name and field name (name convention). Otherwise it is hard to work with many indexes in data base. So, I would suggest to rename your Indexes to be sure.
     
    Andrii Ozemko, Oct 29, 2019 IP
    JEET likes this.
  3. JEET

    JEET Well-Known Member

    Messages:
    2,395
    Likes Received:
    132
    Best Answers:
    2
    Trophy Points:
    185
    #3
    oh, thanks for that :)
    But is it just naming convention, or is that how database system uses to identify which index to use?
     
    JEET, Oct 29, 2019 IP
  4. #4
    It is just naming convention. It allows you to distinguish different objects (indexes) in the database. Some time later you can decide to update indexes, and naming convention will help you to be sure that you are updating the right index.
    About MySQL, I think it should be OK if you created two indexes with the same name but for different tables. It is similar when you created field with the same name "username" in the both tables.
     
    Andrii Ozemko, Oct 29, 2019 IP
    JEET likes this.
  5. JEET

    JEET Well-Known Member

    Messages:
    2,395
    Likes Received:
    132
    Best Answers:
    2
    Trophy Points:
    185
    #5
    Many thanks for your help :) I think I understand this concept now. Thank you :)
     
    JEET, Oct 29, 2019 IP
  6. Andrii Ozemko

    Andrii Ozemko Greenhorn

    Messages:
    37
    Likes Received:
    8
    Best Answers:
    1
    Trophy Points:
    23
    #6
    you are welcome
     
    Andrii Ozemko, Oct 29, 2019 IP
    JEET likes this.
  7. JEET

    JEET Well-Known Member

    Messages:
    2,395
    Likes Received:
    132
    Best Answers:
    2
    Trophy Points:
    185
    #7
    I decided to create indexes like this:

    create index ct_username on customerTable ( username, password )
    create index pt_username on profileTable ( username )

    Thanks for your help :)
     
    JEET, Oct 29, 2019 IP
  8. Andrii Ozemko

    Andrii Ozemko Greenhorn

    Messages:
    37
    Likes Received:
    8
    Best Answers:
    1
    Trophy Points:
    23
    #8
    that looks better. In addition, I cannot recommend using short table names like 'ct' and 'pt'. It could mislead. One day you can create new table 'portfolioTable'. It is better to use full table name.
     
    Andrii Ozemko, Oct 29, 2019 IP
    JEET likes this.
  9. JEET

    JEET Well-Known Member

    Messages:
    2,395
    Likes Received:
    132
    Best Answers:
    2
    Trophy Points:
    185
    #9
    Nice point. Will use full table names instead.
     
    JEET, Oct 29, 2019 IP