#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    165
    Rep Power
    11

    Do you need column indexes with a multi-col unique index?


    Hi all,

    Google didn't help much with this, so I'm turning to you for an answer. I'm trying to make sure that my tables are built as efficiently as possible. With that, I'm building a new table:

    CREATE TABLE student_complete (
    id int(11) unsigned NOT NULL AUTO_INCREMENT primary key,
    completed_date datetime,
    topic_id int(11) not null,
    student_id int(11) not null,
    school_id int(11) not null,
    content_id int(11) not null,
    passed tinyint(3) not null default 0,
    UNIQUE KEY student_school_content (student_id,school_id,content_id)
    ) ENGINE=InnoDB;

    Do I also need to add keys/indexes to the student_id, school_id, and content_id columns? It doesn't seem so if all of my queries will hit student_id, school_id, and content_id in the were clause (in that order) regardless of reading or writing.

    Thanks!

    Mike
    Last edited by storemike; September 16th, 2013 at 12:30 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    Originally Posted by storemike
    ...if all of my queries will hit student_id, school_id, and content_id in the *were clause (in that order) regardless of reading or writing.
    *WHERE clause (FTFY)

    the answer is no, if by "all" you mean all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    165
    Rep Power
    11
    Originally Posted by r937
    *WHERE clause (FTFY)

    the answer is no, if by "all" you mean all
    yep, were should be where. And thanks for the confirmation.
    What about if I'm using REPLACE INTO? The individual keys would not be needed there, either, right?

    Mike
    Last edited by storemike; September 16th, 2013 at 12:47 PM.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    right

    Comments on this post

    • storemike agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    165
    Rep Power
    11
    Originally Posted by r937
    right
    thanks again, r937!

IMN logo majestic logo threadwatch logo seochat tools logo