SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    740
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Question Question about MySQL Indexes

    Hi

    So if I have a table with a structure as below:

    Code:
    CREATE TABLE `employee` (
      `id` INT(10) NOT NULL AUTO_INCREMENT,
      `full_name` INT(64) NOT NULL,
      `email` VARCHAR(255) NOT NULL,
      `mobile` VARCHAR(64) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `email` (`email`),
      UNIQUE KEY `mobile` (`mobile`)
    ) ENGINE=MYISAM DEFAULT CHARSET=latin1
    and I run a query

    Code:
    SELECT * FROM employee WHERE email='abc@example.com'

    Will the index work in this case? (notice the unique index assigned)


    Thanks for any inputs.

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes it will use the unique index on email. If you are not sure you can always use EXPLAIN SELECT * FROM employee WHERE email='abc@example.com' to see how this is being accessed.

  3. #3
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    740
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    What if I have used the INDEX key instead of UNIQUE, would it still work?

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes, the only difference between a regular index and a unique index is that the unique index adds a unique constraint on this column. A regular index performs in exactly the same way but without the unique constraint. So you can have many logins using the same email.

  5. #5
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    740
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the explanation sir


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
  •