Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Posts
    77

    Unanswered: what column to index?

    Hi to all,


    I have 2 tables which are named "items" and "categories". Their structure is this:

    items table:

    item_id int not null auto_number primary key
    item_name varchar(50)
    item_description varchar(30)
    item_category int not null

    categories table:

    cat_id int not null auto_number primary key
    cat_name varchar(30)

    If I issue a join query on those 2 tables, like this one:

    SELECT
    item_id,
    item_name,
    item_cost,
    item_unit,
    cat_name
    FROM items
    INNER JOIN categories
    ON items.item_category = categories.cat_id;

    Since this query is used a lot, which field do I need to create an INDEX with? Is it the item_category field in the "items" table?

    Does a field have an INDEX automatically if it is a primary key field?
    How about UNIQUE fields?

    thanks and god bless.
    Programming is fun!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Panoy
    Is it the item_category field in the "items" table?
    yes


    Quote Originally Posted by Panoy
    Does a field have an INDEX automatically if it is a primary key field?
    yes


    Quote Originally Posted by Panoy
    How about UNIQUE fields?
    yes


    fyi, ansi sql knows nothing about indexes, which are dbms implementations
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    77
    Since I am using MySQL, and if I happen to put a FOREIGN KEY constraint on the "items" table, would the INDEX then be automatically created too?

    From what I understand in MySQL's manual, it does seem so, but it is nice to have read it from other opinion as well.

    thanks
    Programming is fun!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    only for innodb tables, and only after version 4.1.2, the FK index is created automatically

    still wouldn't hurt to code it yourself, because that way you can name it (in case you have to drop it)
    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
  •