index confusion

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
permutations
Forum Commoner
Posts: 52
Joined: Sat Dec 07, 2002 11:45 am

index confusion

Post by permutations »

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:

Code: Select all

KEY їindex_name] (index_col_name,...)
INDEX їindex_name] (index_col_name,...)
I think the difference can't be uniqueness because UNIQUE is also an option:

Code: Select all

UNIQUE їINDEX] їindex_name] (index_col_name,...)
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:

Code: Select all

PRIMARY KEY (CustID)
KEY PRIMARY_KEY (CustID)
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:

Code: Select all

їCONSTRAINT symbol] FOREIGN KEY їindex_name] (index_col_name, ...) їreference_definition]
CHECK (expr)
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!!
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Hi, there's a lot of detail in the manual pages but they do tend to give a lot of information about the syntax:
http://www.mysql.com/doc/en/CREATE_TABLE.html
permutations wrote:1. What is the difference between a KEY and an INDEX?
From the manual:
mysql manual wrote:KEY is a synonym for INDEX.
permutations wrote: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?
How MySQL Uses Indexes:
http://www.mysql.com/doc/en/MySQL_indexes.html
permutations wrote:3. Dreamweaver generated some MySQL code that looked like this:

Code: Select all

PRIMARY KEY (CustID) 
KEY PRIMARY_KEY (CustID)
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?
See answer to 1 and 2.
permutations wrote:4. How do you use an index or key?
See 2.
permutations wrote:My tables have foreign keys in them. Does that mean I need this FOREIGN KEY statement? What is the CONSTRAINT parameter?
It depends on which table type you are using:
http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html
http://www.mysql.com/doc/en/InnoDB.html
http://www.mysql.com/doc/en/example-Foreign_keys.html
mysql manual wrote:In MySQL Version 3.23.44 or later, InnoDB tables support checking of foreign key constraints. See section 7.5 InnoDB Tables. Note that the FOREIGN KEY syntax in InnoDB is more restricted than the syntax presented above. InnoDB does not allow index_name to be specified, and the columns of the referenced table always have to be explicitly named. Starting from 4.0.8 InnoDB supports both ON DELETE and ON UPDATE actions on foreign keys. See the InnoDB manual section for the precise syntax. See section 7.5 InnoDB Tables. For other table types, MySQL Server does parse the FOREIGN KEY, CHECK, and REFERENCES syntax in CREATE TABLE commands, but without further action being taken. See section 1.7.4.5 Foreign Keys.
permutations wrote: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.
It's probably because these relate specifically to InnoDB tables and they are not the default. If you wanted to use these it might be an idea to do some searching for SQL tutorials. From SQL in a Nutshell (published by O'Reilly):
SQL in a Nutshell wrote:When specifying a FOREIGN KEY, the table and columns that maintain referential intergrity may be specified using the REFERENCES clause. It can only reference columns that are defined as a PRIMARY KEY or UNIQUE index on the referencing table.

The CHECK constraint ensures that a value inserted into the specified column of the table is a valid value based on the CHECK constraint.
permutations wrote: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.
I would disagree although you may need a SQL manual to run alongside to fill in some areas since the MySQL manual deals primarily with how MySQL does things not with SQL in general.

Mac
permutations
Forum Commoner
Posts: 52
Joined: Sat Dec 07, 2002 11:45 am

still confused

Post by permutations »

You referenced this as an answer to my questions #2, 3, and 4. I've read this section at least half a dozen times, and did so before I posted these questions. So I remain confused.

Perhaps someone else won't mind taking the time to post actual explanations for me.

P.S. How did you get the quote to say "permutations wrote:" rather than just "Quote:"?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

permutations wrote:Perhaps someone else won't mind taking the time to post actual explanations for me.
It wasn't intended to be a RTFM post and I had no idea that you had already looked at the MySQL stuff on indexes. I did actually spend some time looking for links that might help answer your questions because there's no point me (or anyone else) writing out an explanation if we believe it's already been explained elsewhere.

Maybe this will help explain what an index is further:
http://databases.about.com/library/glos ... erms=index

The quoting thing is a bit of bbcode:
faq.php?mode=bbcode#4

Perhaps you wouldn't mind accepting answers courteously and will take the time to realise that unless you specifically say 'I've already looked at x' we have no idea that you have.

Mac
permutations
Forum Commoner
Posts: 52
Joined: Sat Dec 07, 2002 11:45 am

Post by permutations »

That was just my frustration coming out, sorry. I had spent hours and hours trying to understand it myself before I posted the questions--reading the same sections of the MySQL manual over and over. I signed up with the MySQL mailing list and received clarification there, so I'm on a surer footing now. Thank you for the links. I'll check them out.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

And I was cranky, I'm glad you got the information you needed.

Mac
Post Reply