Page 1 of 1
Need clarification: keys
Posted: Mon Aug 15, 2005 7:47 am
by raghavan20
I have heard of primary and foreign keys but I do not understand what 'keys' mean.
Code: Select all
mysql> SHOW CREATE TABLE ibtest11c\G
*************************** 1. row ***************************
Table: ibtest11c
Create Table: CREATE TABLE `ibtest11c` (
`A` int(11) NOT NULL auto_increment,
`D` int(11) NOT NULL default '0',
`B` varchar(200) NOT NULL default '',
`C` varchar(175) default NULL,
PRIMARY KEY (`A`,`D`,`B`),
KEY `B` (`B`,`C`),
KEY `C` (`C`),
CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
REFERENCES `ibtest11a` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB CHARSET=latin1
1 row in set (0.01 sec)
1. I do understand that the primary key is a combination of several fields and there are two foreign keys declared.
2. I want to know the meaning of:
KEY `B` (`B`,`C`),
KEY `C` (`C`),
Posted: Mon Aug 15, 2005 7:53 am
by feyd
keys are data cached seperately from the table data. The database engine uses the keys whenever possible which makes a query perform faster because it has to look at a small amount of data in comparison. This is why a caution is talked about where you use keys for all fields and it making queries run slower.
Posted: Mon Aug 15, 2005 8:14 am
by raghavan20
How is this different from the index?
Why do they have two separate statements?
KEY `B` (`B`,`C`),
KEY `C` (`C`),
What is it defined inside the braces and outside the braces?
Is there a tutorial available to learn more on this? Its very hard to find results on key mysql
Posted: Mon Aug 15, 2005 8:27 am
by feyd
indexes are a special kind of key, but a key none-the-less.
CREATE TABLE wrote:- KEY is normally a synonym for INDEX. From MySQL 4.1, the key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.
- In MySQL, a UNIQUE index is one in which all values in the index must be distinct. An error occurs if you try to add a new row with a key that matches an existing row. The exception to this is that if a column in the index is allowed to contain NULL values, it can contain multiple NULL values. This exception does not apply to BDB tables, for which an indexed column allows only a single NULL.
- A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY. If you don't have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY.
- In the created table, a PRIMARY KEY is placed first, followed by all UNIQUE indexes, and then the non-unique indexes. This helps the MySQL optimizer to prioritize which index to use and also more quickly to detect duplicated UNIQUE keys.
- A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attribute in a column specification. Doing so only marks that single column as primary. You must use a separate PRIMARY KEY(index_col_name, ...) clause.
- If a PRIMARY KEY or UNIQUE index consists of only one column that has an integer type, you can also refer to the column as _rowid in SELECT statements (new in MySQL 3.23.11).
- In MySQL, the name of a PRIMARY KEY is PRIMARY. For other indexes, if you don't assign a name, the index is assigned the same name as the first indexed column, with an optional suffix (_2, _3, ...) to make it unique. You can see index names for a table using SHOW INDEX FROM tbl_name. See Section 13.5.4.11, “SHOW INDEX Syntax”.
Posted: Mon Aug 15, 2005 8:49 am
by raghavan20
Now, will you say that primary key is also a index.
If they normally say that index is used to make a search faster.
why do we need more indexes when we have primary keys in place?
keys are data cached seperately from the table data
can I say that primary key field/fields data are cached separately for searching?
I think I am a little confused. I will find an article more on indexes.
Posted: Mon Aug 15, 2005 9:03 am
by timvw
raghavan20 wrote:Now, will you say that primary key is also a index.
If they normally say that index is used to make a search faster.
why do we need more indexes when we have primary keys in place?
Well, it's mentionned in the quote from feyd: A table can have only
one PRIMARY KEY.
More general, a primary key could be defined as: a minimal collection of (one or more) attributes/columns that allow you to uniquely identify each entity/row.
For example, each message is identified by it's message_id. But sometimes, you want to search the messages based on the message_content. Therefor it can be handy to have an extra index on that too.
Posted: Mon Aug 15, 2005 9:10 am
by raghavan20
If you are not bothered with my repeated qs, here is one again
table1: userId, userName, city, articleText(fields for just discussion sake)
userId: primary key
index: articleText
query:
select * from `table1` where `articleText` like '%Mysql keys%'
Normally, the engine should find the table, find the field articleText and look for entries which have the words 'Mysql keys'
now if we have an index defined, we have all rows of the field 'articleText' cached somewhere. Now, the database engine finds whether there is an index available, if found it goes to cached data and finds matched entries.
I dont see how the use of index makes the search faster than a normal serach?
Posted: Mon Aug 15, 2005 9:27 am
by timvw
Here is an example:
You have an array with values random between 0 and 100. You want to test if there is a row with value 75.
Solution: you search each element, and see if it has value 75.
Now, if you had the same array, but ordered, you could perform a binary search. Well, that is the same principle that applies for a binary index in your database table. I'm pretty sure you will find explanation for other indexes too..
Posted: Mon Aug 15, 2005 9:37 am
by nielsene
If I recall correctly, "key" in MySQL is actually used for enforcing a uniqueness constraint on the column. Most of other DBMS's would use "UNIQUE" instead of "KEY" here. In most it also has the side-effect of creating an index automatically as its one of the easiest ways to maintain the uniqueness constraint.
MySQL's use of it is almost like Tutorial D's "Candidate Key", however, however I don't beleive MySQL forces a KEY column to NOT NULL, so its not a true key in any relationally correct since of the work. This is a place where MySQL lets some of its "innards" show through -- the fact that uniqueness is implemented via an index shouldn't really be exposed as the implementation could change, but the intent of the schema designer was uniqueness....
Posted: Mon Aug 15, 2005 9:42 am
by raghavan20
Indexes order a set of fields in a way it could be searched faster. I think different indexing techniques are available.
Is B-tree index the common one?
Is this the same as binary search? I think binary search can be used against only numbers.
Is this the one where we split the whole set into two; compare whether the search element is greater or lesser than the middle element and move left or right depending on the result recursively until we find the item.
Posted: Mon Aug 15, 2005 9:59 am
by nielsene
Yes a B-Tree is the most common index type -- it allows for ordering and equality tests. (Ie the index can be used when the operator in the comparison/search is any of '<,<=,<>,=,>,>='). Its a Balance Tree (similar to a normal binary tree, but does some "rotations" to avoid the possible worst case behavoir of a regular binary tree (for instance if you add a sorted list, in order, to a regular binary tree, you end up with on long unbranching chain. if you add it to the B-Tree it rotates different nodes to keep the tree as close to "Complete" or "Full" as possible, ie maximal branching.
Some DBMS's also offer R-Tree (I'm not to familiar with these, but its a index type used for 2-dimensional data, and for checking for overlapping/non-overlappying regions, I think)
Another common one is a simple hash index. It can only test for equality or inequality; It can not test for ordering.
Posted: Mon Aug 15, 2005 2:58 pm
by raghavan20
anybody can tell me the difference between the data types blob and text?
When I was creating back up script, the text data fields are shown as blob, why?
Posted: Mon Aug 15, 2005 3:08 pm
by feyd
they are largely the same. blob just has the binary attribute on automatically, last I saw..