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.

yes


yes


yes


fyi, ansi sql knows nothing about indexes, which are dbms implementations

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

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)