|
|
Posted by Robson on 10/06/05 16:12
Ok I understand.
Furthermore, is it important to declare a primary key as an index or is it
implicit when you declare it as a primary key ?
Thanks for your kind help :)
Regards
"Stefan Rybacki" <stefan.rybacki@gmx.net> a ιcrit dans le message de news:
3qira3Ff7pgoU1@individual.net...
> 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
[Back to original message]
|