Professional Web Applications Themes

Need to understand INDEX and PKEYS - MySQL

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

  1. #1

    Default 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


    Simon Guest

  2. #2

    Default Re: Need to understand INDEX and PKEYS

    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 [url]http://www.tonymarston.net/php-mysql/database-design.html[/url] for
    some details on relational database design.

    --
    Tony Marston
    [url]http://www.tonymarston.net[/url]

    "Simon" <spambucketexample.com> wrote in message
    news:3s10rtFlmesfU1individual.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 Guest

  3. #3

    Default Re: Need to understand INDEX and PKEYS

    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.
    Christian Kirsch Guest

  4. #4

    Default Re: Need to understand INDEX and PKEYS

    >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
    Gordon Burditt Guest

  5. #5

    Default Re: Need to understand INDEX and PKEYS

    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.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  6. #6

    Default Re: Need to understand INDEX and PKEYS

    Christian Kirsch <ckbru6.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
    Axel Schwenke Guest

  7. #7

    Default Re: Need to understand INDEX and PKEYS


    "Peter H. Coffin" <hellsopninehells.com> wrote in message
    news:slrndlnr24.8mk.hellsopothin.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

    [url]http://www.tonymarston.net[/url]


    > 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

    Tony Marston Guest

  8. #8

    Default Re: Need to understand INDEX and PKEYS


    "Axel Schwenke" <axel.schwenkegmx.de> wrote in message
    news:ur2hjd.edh.lnidefix.xl.local...
    > Christian Kirsch <ckbru6.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

    [url]http://www.tonymarston.net[/url]



    Tony Marston Guest

  9. #9

    Default Re: 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

    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


    Simon Guest

  10. #10

    Default Re: Need to understand INDEX and PKEYS

    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.
    Bill Karwin Guest

Similar Threads

  1. Settle a debate - to index or not to index?
    By Duke Ionescu in forum MySQL
    Replies: 1
    Last Post: March 23rd, 05:02 AM
  2. Replies: 4
    Last Post: August 3rd, 03:11 PM
  3. Index Topics and Index References
    By Tom_Cusick@adobeforums.com in forum Adobe Indesign Windows
    Replies: 2
    Last Post: July 8th, 07:20 PM
  4. Newb query: index.htm & index.php & the server default
    By Lab309 in forum PHP Development
    Replies: 7
    Last Post: September 22nd, 02:08 PM
  5. Create index VS Set Index Enabled - IDS 7.31
    By Stéphane Gadoury in forum Informix
    Replies: 2
    Last Post: July 21st, 02:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139