SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2001
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Indexes and Primary Keys

    Just begun work on how to integerate the mighty PHP with mySQL. There's a couple of things which I don't really understand and they are as the topic suggests: indexes and primary keys. Seeing as the people in here are no different than me(well I like to think so); I was wondering whether any one of you helpful lot could care and tell me in English terms what these two mean when it comes to database tables...
    Bardius

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well a primary key is central to the relational theory and relational database design. Every row (also known as a record or tuple) in a table (also known as a relation) mut be uniquely identified by a primary key. For example, in a relational scheme the first normal form says that each row of a table must be uniquely identified by its primary key:

    Code:
    This is not allowed in a relational database. (two records the same in a table).
    
    id   name   colour
    ------------------
    1    foo    red
    1    foo    red
    
    further, if the primary key is defined as id then this is also not allowed
    
    id   name   colour
    ------------------
    1    foo    red
    1    bar    blue
    
    Because each record must have a unique primary key.
    Sometimes we can make a primary key be a composite of two or more attributes of a table. For example if we defined a composite primary key for the above table of (name, colour) then the second example would not break the first normal form because foo,red is not the same as bar,blue. So in short, the primary key is the unique identifier of a row in a table.

    Indexes are not mandatory in your database design and do not relate to the relational theory. Indexes are more a performance enhancing tool for your database. An index is just like an index in a book. If we want to quickly reference a specific item, we look in the index to see which pages that item is mentioned. Same thing with a database table. We can index certain attributes in our table so that we can look up records by that attribute quickly.

    For example, in the above example, we might create an index on the attribute colour. This will actually create an index table in our database that will hold a pointer to each record in the table we have indexed along with its value for colour. This speeds up lookups where we are searching for records by colour. Say we want to find all the records with colour='red'. First, the database will lookup the index and see which records have a value of 'red' for colour. Then it will look up the main table, jumping directly to the relevent records and grabbing all the data for that record.

    The down side to using indexes is while they speed up SELECTs they slow down INSERT, UPDATE and DELETE queries, because the record needs to be changed in the table and in the index/s.

    As a rule of thumb, if you have a query that could do with some speeding up, then create an index on all of the attributes named in the WHERE clause in your query.

    Eg,
    SELECT * FROM TableName
    WHERE foo=$foo
    AND bar=$bar

    Here you would want to create an index on (foo,bar)

    There is a really good introductory article on indexes at www.phpbulder.com. However, before you worry about indexes, it is vital that you understand database design and data normalisation to the general third normal form (3NF). See Skunks links (look in my signature) for more resources. Cheers
    Last edited by freakysid; Aug 31, 2001 at 01:04.

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2001
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So in other words, a field that's set as the Primary Key is basically a unique field? The only difference (at least to my eye) between the two is that you can have more than unique field and only one sole field set as the primary key?
    Bardius

  4. #4
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Bardius
    So in other words, a field that's set as the Primary Key is basically a unique field? The only difference (at least to my eye) between the two is that you can have more than unique field and only one sole field set as the primary key?
    yeah, you got it.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR


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
  •