Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Bucharest
    Posts
    37

    Post Unanswered: how to put indexes

    Here is the following problem:

    email
    ------
    id
    email

    list
    ------
    id
    name

    email_to_list
    ------
    email_id
    list_id

    On `email_to_list` what is your recommendation on how to put indexes. I need to put an UNIQUE on (email_id, list_id). Do i also need to put INDEX on email_id and list_id separately? Presuming i make a lot of JOIN queries on these keys.

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Zamolxe
    I need to put an UNIQUE on (email_id, list_id).
    i would just make them the primary key instead

    Quote Originally Posted by Zamolxe
    Do i also need to put INDEX on email_id and list_id separately?
    no!!

    primary keys get a unique index by default

    but you might add an index on ( list_id , email_id )

    this index, along with the primary key index, ensure that all queries will use one or the other index, and both of them are likely to be a covering index
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •