Topic: The Mystery of Column Indexes

I am pretty green with this dba stuff. I get the gyst of what indexes do.. they are supposed to optimize performance on repetitive find queries. But I dont understand the SQL lingo behind them..

For example, I look at my db table and I see that my indexed columns show up, but under the column named "Null" they both mark "YES".. does this mean my indexes are Null?

There are other niggling quesitons like this, but more than anything, I am just curious

Why and when do you ues indexes?

Re: The Mystery of Column Indexes

Under mysql for example, when you do 'show table', the column entitled 'Null' means whether or not NULLs are allowed as entries for that field.  This is unrelated to indexes.
The best analogy to indexes I can muster is this: consider placing 100k objects in an array.  Now, you want to find all objects where attribute X is 5.  You have to iterate all 100k objects to find the results.  Now, what would have happened if you were to have put your 100k objects in a hashtable, hashed by attribute X?  You would probably be able to retrieve all objects where X == 5 in about 1/1000th of the time.
I wouldn't worry about indexing any tables unless you have more than 10k rows in a table or you find certain queries becoming a performance bottleneck.

BrewControl.com - Brewery and Brewpub management powered by RoR

Re: The Mystery of Column Indexes

Thanks for the clarification bkrahmer.

Would you recommend any good books on SQL? I feel it is definitely the weakest link in my programming skills

Re: The Mystery of Column Indexes

Sure.  As for books, not me.  Somebody else?

BrewControl.com - Brewery and Brewpub management powered by RoR