index confusion
Posted: Tue Feb 25, 2003 3:35 pm
I hope someone here can help me. I've been banging my head against a wall all day long. I'm fairly new to MySQL programming, and I lack some basic information. The mysql.com site neglects to define key terms and in general drives me nuts. I bought a book on PHP and MySQL development on amazon.com, but it's pretty useless--does not give the MySQL detail I was hoping for.
In the CREATE TABLE statement, there are options to create keys and indexes, and this is where I'm confused. I'll try to be as clear as possible in my questions because I'm really desperate for help. I can't find anything in my reference books or on the internet that explains these things.
1. What is the difference between a KEY and an INDEX? These are two separate options:
I think the difference can't be uniqueness because UNIQUE is also an option:
2. In the UNIQUE option above and in the FULLTEXT option, one of the optional parameters is [INDEX]. What does this mean? How is the option different if the word "INDEX" is included?
3. Dreamweaver generated some MySQL code that looked like this:
What is the point of the second option--the KEY statement? Why create two keys on the same column? Is the KEY option necessary if you use the PRIMARY KEY option?
4. How do you use an index or key? If you use the column on which the key/index is based (are these terms interchangeable??), does MySQL use the index?? Using the above example, do I have to use the names PRIMARY or PRIMARY_KEY to use the indexed version of CustID? If I do a join using CustID, is MySQL reading the table sequentially and bypassing the index file?
5. There are two other options in the CREATE TABLE statement that I don't understand at all:
My tables have foreign keys in them. Does that mean I need this FOREIGN KEY statement? If so, then how do I use it? What is the CONSTRAINT parameter? What is "symbol"? What is "reference_definition"?
And finally, what is "CHECK (expr)"? I just love how the mysql.com manual lists these without a single explanation or example. I see they are selling a printed version of their online manual for big bucks. That's precious. It's the worst documentation I've ever seen.
Thank you hugely and VASTLY in advance if you can clear up these issues for me. PLEASE help. I am desperate!!
In the CREATE TABLE statement, there are options to create keys and indexes, and this is where I'm confused. I'll try to be as clear as possible in my questions because I'm really desperate for help. I can't find anything in my reference books or on the internet that explains these things.
1. What is the difference between a KEY and an INDEX? These are two separate options:
Code: Select all
KEY їindex_name] (index_col_name,...)
INDEX їindex_name] (index_col_name,...)Code: Select all
UNIQUE їINDEX] їindex_name] (index_col_name,...)3. Dreamweaver generated some MySQL code that looked like this:
Code: Select all
PRIMARY KEY (CustID)
KEY PRIMARY_KEY (CustID)4. How do you use an index or key? If you use the column on which the key/index is based (are these terms interchangeable??), does MySQL use the index?? Using the above example, do I have to use the names PRIMARY or PRIMARY_KEY to use the indexed version of CustID? If I do a join using CustID, is MySQL reading the table sequentially and bypassing the index file?
5. There are two other options in the CREATE TABLE statement that I don't understand at all:
Code: Select all
їCONSTRAINT symbol] FOREIGN KEY їindex_name] (index_col_name, ...) їreference_definition]
CHECK (expr)And finally, what is "CHECK (expr)"? I just love how the mysql.com manual lists these without a single explanation or example. I see they are selling a printed version of their online manual for big bucks. That's precious. It's the worst documentation I've ever seen.
Thank you hugely and VASTLY in advance if you can clear up these issues for me. PLEASE help. I am desperate!!