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

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

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

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