Page 1 of 1

what does an INDEX mean?

Posted: Wed Jul 27, 2005 12:10 pm
by php12342005
I created a table "purchase" by following SQL:
(acturally I copied code somewhere and modified it)

CREATE TABLE IF NOT EXISTS purchase
(
custid VARCHAR(20) NOT NULL,
discript VARCHAR(50),
INDEX IND_KEY(custid),
FOREIGN KEY(custid) REFERENCES customer(custid) ON DELETE CASCADE
)TYPE = INNODB;

the table has been created (table main exists).

if I delete line "INDEX IND_KEY(custid)," in SQL above, the SQL can not be executed properly (error).

I am wondering that why I must use an INDEX for foreign key?
it looks useless, what is an INDEX used for?

can you explain?

in phpMyAdmin, I can not see an undersore in field "custid", what does undersore indecate in phpMyAdmin?

thanks

Posted: Wed Jul 27, 2005 12:18 pm
by nielsene
I'm not a MySQL expert, but I beleive it uses to for two things
1) it enforces the UNIQUENESS contraint on the primary key (I beleive KEY and INDEX are synonomous in MySQL).
2) it generates a faster data-access method, allow the database to avoid a full table scan one many operations, leading to big boosts in selects, requirely slightly more work on insert/updates/deletes.

I suspect its 1) that breaking stuff completely.