OIC.
Thanks for the clarification. I wasn't aware that MySQL (or any other SQL
for that matter) stored records in a 'physical order'. I know in M$ SQL
there is no such concept as records are constantly being reused as the
contents are deleted. (and other optimization schemes are in place) These
effectively destroy the concept of a 'physical' order of the records for all
practical purposes.
I've always considered all indexes to be o the same level and neither
primary nor secondary to any other index. Primary has always been used to
designate the candidate key that the DBA has designated as the Primary Key
but other than that, I've not heard this terminology used in this way.
in any case, I would be interested to know if MySQL stores records in any
sort of consistent (FIFO, LIFO) manner. I would imagine that it greatly
depends on the table type you are using. (But hey, I've been wrong before.)
:)
Cal
http://www.calevans.com
-----Original Message-----
From: Thalis A. Kalfigopoulos [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 27, 2001 12:08 PM
To: Cal Evans
Cc: Duke; [EMAIL PROTECTED]
Subject: RE: keywords in CREATE TABLE?
I'll try to depict this here, though ascii form doesn't really help (this is
where a blackboard and some chalk do a better job)
Assume you have a table which is physically stored as:
ID TAG
-------------
1 lala
2 koko
3 haha
4 toto
If I create an index on ID, then this would be considered to be a primary
index. (Even more if ID is unique you'd call that field an ordering KEY and
if ID is not unique you'd call the field an ordering FIELD but I have only
found this terminology once so I wouldn't bet on it). The main idea is that
a primary index is an index that allows me to read the records in an order
that closely corresponds to the physical order.
You B+ would look like:
/ root \
/ / \ \
/ / / \ \
| | | | | <--Notice that the ptrs from the
| | | | | leaf nodes go straight down
[physical blocks: records sorted as in index]
On the other hand if I create an index on TAG, it would be a secondary
index. That is: the leaf level of my B+ would have the records sorted as
(haha->koko->lala->toto) whereas this is not their physical order. Secondary
indeces are also called non-clustered indeces thus leading to clustering
index implying a primary index (although I don't like this when the ID for
example if unique)
(no drawing for the seconday B+ cause I found it impossible ;-)
>From my understanding I don't see Mysql using primary index as described
above. Only secondary indeces on unique or non-unique fields.
Awaiting comments.
regards,
thalis
On Tue, 27 Mar 2001, Cal Evans wrote:
> How do you differentiate between primary and secondary indexes? I'm not
> familiar with the distinction.
>
> Cal
> http://www.calevans.com
>
>
> -----Original Message-----
> From: Thalis A. Kalfigopoulos [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, March 27, 2001 10:55 AM
> To: Cal Evans
> Cc: Duke; [EMAIL PROTECTED]
> Subject: RE: keywords in CREATE TABLE?
>
>
> On Tue, 27 Mar 2001, Cal Evans wrote:
>
> > Key = Creates an index
> > Index = Same as above? (Not sure, the section of the manual I just
glanced
> > at seems to indicate that they are the same thing)
> > PRIMARY KEY = Creates a primary key for the table. Each table should
have
> a
> > primary key and it should be unique for that row in that table. (For
more
> on
> > PK theory, checkout
> >
>
http://www1.fatbrain.com/asp/BookInfo/BookInfo.asp?theisbn=0764504150&from=T
> > GD352 )
> > UNIQUE = Like primary key in that it creates a unique index on that
field
> > but it is not considered a primary key.
>
> Hello Cal,
>
> First: I don't think it is necessary that each table should have a primary
> key.
>
> Second: there seems to be some confusion between the concepts of primary
key
> and primary index.
> >From the theory (at least the one I know) a primary index is NOT an index
> on a primary key. Primary key is just the one of all the candidate keys
that
> was chosen by the DBA. Primary index on the other hand is the index whose
> search key specifies the sequential order of the file.
> So someone please bother to explain what happens exactly in Mysql. From
what
> I gather, when you specify a field(s) as PK, you just get another
secondary
> index on that field.
> So we come to the conclusion that primary key is just another unique key
> with a special name. Therefore the concept of primary index doesn't exist
in
> Mysql and the only flavor you have is secondary indexes either on unique
or
> non-unique keys.
>
> Are the above conclusions correct?
>
> regards,
> thalis
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
