|
|
Posted by Stefan Rybacki on 10/07/65 11:28
Robson wrote:
> Hi,
>
> I'm trying to setup a decent DB and I've just discovered the INNODB type
> which is to my mind very interesting. Anyway I want to use foreign keys in
> order to make easier the updating and deletion of my DB elements and in the
> examples I have, I always get to declare indexes before declaring my foreign
> keys. A simple example :
>
> CREATE TABLE product (
> category INT NOT NULL,
> id INT NOT NULL,
> price DECIMAL,
> PRIMARY KEY(category, id)
> ) TYPE=INNODB;
>
> CREATE TABLE customer (
> id INT NOT NULL,
> PRIMARY KEY (id)
> ) TYPE=INNODB;
>
> CREATE TABLE product_order (
> no INT NOT NULL AUTO_INCREMENT,
> product_category INT NOT NULL,
> product_id INT NOT NULL,
> customer_id INT NOT NULL,
> PRIMARY KEY(no),
> INDEX (product_category, product_id),
> FOREIGN KEY (product_category, product_id)
> REFERENCES product(category, id)
> ON UPDATE CASCADE ON DELETE RESTRICT,
> INDEX (customer_id),
> FOREIGN KEY (customer_id)
> REFERENCES customer(id)
> ) TYPE=INNODB;
>
>
> Why do I have to declare "INDEX (product_category, product_id)" and "INDEX
> (customer_id)" before each foreign key ? I have tried to create the last
> table without these lines and it works fine, so what is the importance of
> this kind of declaration and how does it improve my DB ?
Its because of performance reasons. Indices avoid full table scans. And since you need the
foreign key attributes often on cascades or on joins it makes sense to define an index
over them. In general it makes sense to create an index over attributes you're often ask for.
Regards
Stefan
>
> Thanks very much for taking some of your time to read / answer my post :)
>
> Regards
> Rob
>
>
Navigation:
[Reply to this message]
|