Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2006
    Posts
    18

    Unanswered: Indexing table fields - Why and When?

    Okay, here's another oh, so, basic question -

    What is the purpose of indexing a field (or fields) in a db table? And under what circumstances should one do so? The most I've gotten so far from a google search is "indexing a table speeds the query process." Good to know, but not really helpful in regard to me understanding what types of fields should be indexed.

    I suspect there might have been a collective ***sigh*** of boredom from the members here about my "foreign key" posting, but I'm an American living in the backwaters of the Philippines (have been for the past 10 years) and I've become very interested in all things database related. BUT, living where I do there is virtually no support network here (universities, libraries, physical user groups, etc) that I an go pester with my questions.

    You folks have been VERY helpful with my past two posts and to that I am very greatful. Everyone has been a real If you could continue to bare with me on this, that would be great. Bottom line is that I'm really motivated to learn this correctly; to accomplish that, sometimes must solicit the expertise of the masters rather than just fighting the fogs of frustration.

    On the other hand, if someone would be willing to post a really good (and really understandable) link to a website that would answer some of these most basic of questions that would probably even be better.

    Thanks in advance to all who suffer my learning curve.

    Expatriate

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    an index helps the db engine improve its performance
    this can help in several ways
    - it narrows down the rows it has to process when you use a where or havign clause,
    - it helps where you need a specific row order

    However an index is not a free lunch - it comes at a price

    each index insert, or change to index column requries extra work for the db to place it in the index system.

    if you are running a complex high volume or frequently changing data capture / transaction system then generally you want have as few indexes as you can get away with.

    if you are running data warehouse / data analysis system where the data doesn't change that much (in value or volume) then you can probably index virtually anything that yoiu think may be required for querying or analysis purposes.

    full text invoices are a very powerfull tool, but can cause serious problems.

    so there are no hard and fast rules as to what is the correct way to use indicies. BUT having said that: use as few indicies as you need, pay attention to the cost of the query 'explain' is a good tool for that. and keep an eye of server performance.

    just my 2 1/2d....
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Think of your table as a coffee can (or maybe even a garbage can). Think of the rows in your table as washers (the little "ring thingie" that you put on a bolt or a screw) inside the can... Finding the first, last, or any specific washer inside the can is a BIG job!

    Now let's modify the problem just a bit and add a thread to the problem. Run the thread through all of the washers in the correct order. One end of the thread has the "first" washer, the other end has the "last" washer, and all of the other washers sit on the thread in the specified order.

    Given this example. the thread is an index. It provides an "order" for the rows in the table that is specific to your needs (the sequence that you need the rows), even though physically there is little or no chance that the database will actually keep the rows in that order.

    Note that you can have many threads in this example, and nothing says that they have to traverse the washers in the same (or similar in any way) order. One might sort the washers by size, one by color, another by hardness... The threads have very little to do with each other, except for connecting the washers in some order you determine.

    -PatP

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    clarification:

    thread = piece of string

    NOT

    thread = the little grooves that you find on a bolt or a screw

    your washer/bolt/screw analogy made me immediately think of the second interpretation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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