[nassau]

I have a vague idea of what an SQL index is and what it's good for, but, will someone knowledgable please explain it to me once and for all. My understanding is that an index is.. just that - an index. I understand it helps the database search faster on table-columns that are often searched.

thanks

[Brad]

Your understanding is correct. An index is simply an index that is used for speeding up SQL queries.

That's really all there is to it!

Say you have a large table that is sorted by column A, but not by columns B and C. If you need to do some selects based on the contents of B or C, you can create an index on that column to speed up the search.

[nassau]

but there must be drawbacks to the system, otherwise they would have put automatic indexes (indices?) on all columns, right?

does an index take up much space? what's the drawback?

[Banana]

You just answered it. It does take space, and if you indexed everything, it would get quite confusing (e.g. if you had two indexes relating A with B and A with C, and you need B with C, which index do you want?)

Normally, I index my foreign keys, some relevant data such as dates, a certain person or group that I may use as a criteria in one of my queries.

HTH.

[Brad]

What Banana said. The only drawback is that it uses up more storage.

For small databases, it isn't a problem to have an index on every single column. You can do that if you want to. With larger databases that have thousand or millions or billions of records, though, you have to be a bit more careful with how you index.

[nassau]

but if i have unlimited storage space on my hosting account (which i do), theoretically i should index everything than, heh.

i gather from bananas post that you can combine indices of many columns. i've done that accidentally before and never really understood what i did..

thanks to both of you for the info!

[Banana]

It's not just about storage, but also about performance.

When doing a query, engine (be it SQL, Access, Filemaker or Brad's Brain) runs through every relevant indices and assemble together the results. Now, if indexes are redundant, you can be sure that engines will likely go through them again, and even if they knew better, they would have to make sure they didn't miss out that and this records.

So even with an unlimited storage, I'll come back to check on your database in oh say, 20 years if you have a billion records that has all fields indexed.

Which is why you should index only fields that you mostly likely will be doing search on. Dates and Names are obvious example. But to index street address? Nothanksbyeverymuch!

[nassau]

yea i think i get the idea now.