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
==================