Need to understand INDEX and PKEYS

Hi,

I am not 100% happy with my understanding of indexes and primary keys.

I have a table with ID, Author and Text, (ID is auto increment)

Most of the select I do is "SELECT * from Table where ID = someID"

and then I with the result I do "SELECT Text, Author from Table where Author
= someAuthor"

It all works fine enough, (because I only have 400 records), but I don't
have a primary key or an index.

What primary key/index should I create?

And where could I find a simple tutorial/help on primary keys and indexes to
help me understand them once and for all.

Many thanks

Simon

Every table SHOULD have a primary key. A table may have more than one unique
key (also known as a candidate key) but only one can be marked as the
primary key. All unique keys have the same property - only one entry with
that value is allowed to exist on that table.

An index is a non-unique key. Any number of entries with the same value can
exist on the same table. A table can have any number (zero or more) of
these. You only create an index for performance reasons.

Take a look at http://www.tonymarston.net/php-mysql...se-design.html for
some details on relational database design.

--
Tony Marston
http://www.tonymarston.net

"Simon" <spambucket@example.com> wrote in message
news:3s10rtFlmesfU1@individual.net...
> Hi,
>
> I am not 100% happy with my understanding of indexes and primary keys.
>
> I have a table with ID, Author and Text, (ID is auto increment)
>
> Most of the select I do is "SELECT * from Table where ID = someID"
>
> and then I with the result I do "SELECT Text, Author from Table where
> Author = someAuthor"
>
> It all works fine enough, (because I only have 400 records), but I don't
> have a primary key or an index.
>
> What primary key/index should I create?
>
> And where could I find a simple tutorial/help on primary keys and indexes
> to help me understand them once and for all.
>
> Many thanks
>
> Simon
>

Tony Marston wrote:

> An index is a non-unique key.

That makes me wonder why CREATE INDEX has the optional UNIQUE keyword...

Please answer postings *after* the question - that makes it a lot easier
for others to understand the sequence of questions and answers.

>I am not 100% happy with my understanding of indexes and primary keys.

A primary key is a unique index that in addition is the primary
one. This is similar to one of a set of twins being the real person
and the other one is "just a twin". It's which one you CALL primary
that makes the differenece.

>I have a table with ID, Author and Text, (ID is auto increment)
>
>Most of the select I do is "SELECT * from Table where ID = someID"

A field you use often in a WHERE clause is a good candidate for an index.
Chances are that any field named "ID" is unique and a good candidate
for a primary key. A column that is auto_increment must be unique
and is probably a good candidate for a unique index or primary key.

>and then I with the result I do "SELECT Text, Author from Table where Author
>= someAuthor"

Is this the same table? You got both Text and Author from the first
query above looking up by ID. Or is the point here to find other
records with the same Author as the given ID?

If you often do lookups by Author, that's another candidate for
an index (which might not be unique if, for example, this is a table
of books and an author can write more than one book).

>It all works fine enough, (because I only have 400 records), but I don't
>have a primary key or an index.
>
>What primary key/index should I create?

An auto_increment column like ID MUST have a unique index on it.
Are you sure you don't already have an index on it?
If you often do lookups by Author you may want an index on that also.

>And where could I find a simple tutorial/help on primary keys and indexes to
>help me understand them once and for all.

Gordon L. Burditt

Simon wrote:
> Hi,
>
> I am not 100% happy with my understanding of indexes and primary keys.
>
> I have a table with ID, Author and Text, (ID is auto increment)
>
> Most of the select I do is "SELECT * from Table where ID = someID"
>
> and then I with the result I do "SELECT Text, Author from Table where Author
> = someAuthor"
>
> It all works fine enough, (because I only have 400 records), but I don't
> have a primary key or an index.
>
> What primary key/index should I create?
>
> And where could I find a simple tutorial/help on primary keys and indexes to
> help me understand them once and for all.
>
> Many thanks
>
> Simon
>
>

Simon,

It's a bit confusing when you first start out, but it gets easier.

First of all, an index is just a way to access information faster. It's
like the index in the back of a reference book; you can look something
up in the index and go straight to the page containing the information.
And since the index is maintained alphabetically, you can find what
you're looking for relatively quickly.

A database index is similar. You create the index on one or more
columns; the index will contain a pointer to the row matching that index
entry. If more than one row matches the index value, there will be
multiple entries in the index, each entry pointing to a matching row.
And since the index is maintained alphabetically (or numerically in the
case of numeric index), a binary search on it is pretty quick.

An index can also be specified as UNIQUE, which means that the index
value must be unique in the table.

You can have any number of indexes on a table, in any combination of
unique and non-unique.

Indexes are great for retrieval - they speed things up a lot. However,
you don't get anything for free. When you add an index, you increase
the overhead for inserts and updates. When you insert a record, the
data must also be inserted into the index. And since the index is
maintained alphabetically, it typically means having to rewrite a large
part of the index to the file system (since everything after this entry
must be moved down). Updates may or may not require an index rewrite,
it depends on if the key changes.

So you always have to balance database update performance vs database
read performance. Having an index on every possible WHERE clause value
will speed up read access but slow any updates.

A Primary Key is a way of uniquely identifying a specific row in a
table. It may be a single column such as an author id (which often is
an autoincrement but need not be), or multiple columns (such as date and
room_number for a hotel reservation system). The important thing is
that it identifies a unique row - a request on the primary key will
*never* return more than one row (but may return zero).

Under the covers, MySQL (and other databases) generally use a unique
index to manage a primary key. But where you can have more than one
index, you can have only one primary key.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Christian Kirsch <ck@bru6.de> wrote:
> Tony Marston wrote:
>
>> An index is a non-unique key.

A key is always unique.
There is no such thing as a nonunique key.

> That makes me wonder why CREATE INDEX has the optional UNIQUE keyword...

Because it's the other way round: A KEY is an UNIQUE index.
Indexes can be either unique or nonunique. If an index is
unique, it's sometimes[1] called a key.

[1] "key" is a theoretical construct, "index" a practical one


XL

"Peter H. Coffin" <hellsop@ninehells.com> wrote in message
news:slrndlnr24.8mk.hellsop@othin.ninehells.com...
> On Sun, 23 Oct 2005 14:38:54 +0200, Christian Kirsch wrote:
>> Tony Marston wrote:
>>
>>> An index is a non-unique key.
>>
>> That makes me wonder why CREATE INDEX has the optional UNIQUE
>> keyword...
>
> Tony's perception differs from mine. To me, a key is a column to which
> other tables relate to this one, and that is the only quality that makes
> "keyness".

A key, whether it be a primary key or a candidate key, does not have to be
referenced from other tables in order to be known as a "key". It does not
*have* to be, but it usually is. In that other table it is known as a
"foreign key" as it points to a key in a different (foreign) table. THAT is
the difference between a primary key and a foreign key.

It is also possible for a foreign key to be a primary key at the same time.
It is a "primary" key because it is a unique key for the table in which it
is defined, but it is also a "foreign" key because it points to the primary
key of another table.

--
Tony Marston

http://www.tonymarston.net



> An index is a method of optimizing access by a particular
> column into the table in question, but there may not be any particular
> relationship to other tables for an indexed column. For example, a date
> column may likely be indexed to provide sorted and ranged lists, but is
> rarely used as a join column in any table relationship. However, since
> the key function is almost invariably benefits from speedy access by the
> column, an index is built on the column as a matter of course. Checking
> for uniqueness is easy to do in the routines that maintain the index,
> and while that checking is *very* useful in a "key column" context, it's
> not only applicable in that context, so rolling that functionality into
> the index functions makes a great deal of sense.
>
> --
> Crowds want to beat, journalists deserve to be beaten. Where lies
> the problem?
> -- Lars Syrstad

"Axel Schwenke" <axel.schwenke@gmx.de> wrote in message
news:ur2hjd.edh.ln@idefix.xl.local...
> Christian Kirsch <ck@bru6.de> wrote:
>> Tony Marston wrote:
>>
>>> An index is a non-unique key.
>
> A key is always unique.
> There is no such thing as a nonunique key.

A non-unique key is known as an index. It provides fast access, or a "key",
into a table without requiring a full table scan.

>> That makes me wonder why CREATE INDEX has the optional UNIQUE keyword...
>
> Because it's the other way round: A KEY is an UNIQUE index.
> Indexes can be either unique or nonunique. If an index is
> unique, it's sometimes[1] called a key.
>
> [1] "key" is a theoretical construct, "index" a practical one

Keys cannot be theoretical because they actually exist. Life on Mars is
theoretical.

--
Tony Marston

http://www.tonymarston.net

> Hi,
>
> I am not 100% happy with my understanding of indexes and primary keys.
>
> I have a table with ID, Author and Text, (ID is auto increment)
>
> Most of the select I do is "SELECT * from Table where ID = someID"
>
> and then I with the result I do "SELECT Text, Author from Table where
> Author = someAuthor"
>
> It all works fine enough, (because I only have 400 records), but I don't
> have a primary key or an index.
>
> What primary key/index should I create?
>
> And where could I find a simple tutorial/help on primary keys and indexes
> to help me understand them once and for all.
>
> Many thanks
>
> Simon


Thanks all for the replies and links.

I guess in my case I could/would/should have an Index on ID, but because it
is already auto-increment there is no real need for it, (is isn't it indexed
by default?).
I could also create an index on "Author" as I often call it in my second
query.

My table never gets updated so I guess I could have as many indexes as I
want.

Simon

Tony Marston wrote:
> A non-unique key is known as an index. It provides fast access, or a "key",
> into a table without requiring a full table scan.

A key is a logical concept; it is part of the relational model. It
refers to the way in which a column (or set of columns) is used, to
identify records in a table.

Keys are almost always implemented by an index. An index is a physical
implementation that helps improve the performance of lookups in certain
ways.

There is no relationship in the relational model between keys and
indexes. They are separate concepts. However, it is always the case in
MySQL (and many other RDBMS implementations) that when you define a
primary or foreign key, an index is implicitly created to help enforce
the key in an efficient manner.

You can also create a unique or non-unique index on other columns as
part of your schema creation. These are up to you as the developer, and
they can help improve query performance in some cases.

Again, keys are part of the logical schema design. Indexes are a part
of physical database implementation. I think that's what Christian
meant by theoretical vs. practical.

Regards,
Bill K.