What is an index in table? Related with realworld entitly like librabry. what are the types of indexes? please related this too also with real world entity.
+1
A:
Check this link : Types of Indexes
An Introduction to Clustered and Non-Clustered Index Data Structures
Pranay Rana
2010-06-17 05:55:08
A:
Very briefly, database indexes improve the speed of data retrieval operations. In general, you should have an index on every field that is used in the WHERE, JOIN and ORDER BY clauses of your queries.
Daniel Vassallo
2010-06-17 05:55:33
I don't entirely agree with the last statement ... Indexes (and their type) should be chosen with care depending upon the application/data needs and access.
pst
2010-06-17 05:58:55
@pst: Well yes of course. But I thought it was the quickest "rule of the thumb" to provide to a newcomer to databases... We all agree that this is a huge topic to explore in detail... In any case, **in general**, you *do* need to have an index on those fields.
Daniel Vassallo
2010-06-17 06:02:59
Having an index on every field is like having 3 different phone directories, one for LastName, one for FirstName and one for City. Far better to have the index which is right for your query, so that you can look up "Vassallo->Daniel->Valetta" to find your row.
Rob Farley
2010-06-17 06:03:20
@Daniel - no, it's almost never a good idea, and indexes on single fields will generally only be used when that's the only field needed by a query. Far better is to ask yourself what your ideal index would be in a paper-based system, and create that.
Rob Farley
2010-06-17 06:04:55
@Rob: I didn't intend to say that there should be an index *on every* field... If you're going to have `WHERE city='Valletta'`, I guess there should be an index on `city`... This answer was never intended to be an exhaustive exploration of database indexes, just an attempt to point the OP to a good direction for further research.
Daniel Vassallo
2010-06-17 06:05:50
@Daniel: Sure, but your query is going to be returning fields too, and joining on others, and ordering by something else, so that the index should almost never be just on the one field.
Rob Farley
2010-06-17 06:09:33
@Rob: I can't agree with that *almost never*. Composite indexes serve a totally different purpose. For example if you have a composite index on `(City, TelNumber, Surname)` you wouldn't be able to do a simple `SELECT * FROM users WHERE TelNumber = 12345`... I would actually say the opposite. **In general**, indexes on single fields are often the more useful. Composite indexes (with the exception of covering indexes) are often avoided.
Daniel Vassallo
2010-06-17 06:13:32
@Daniel: But when you write `SELECT Name, AreaCode FROM users WHERE TelNumber = 12345 ORDER BY AreaCode`, then you will want the index on `(TelNumber, AreaCode) INCLUDE (Name)`. If you have separate indexes on TelNumber and AreaCode, then your query will have extra work to do. I think of the paper-based solution, and think "It's all well and good to get the TelNumber entries, but wouldn't it be nice if the entries were already in the right order, and had the Name there as well...."
Rob Farley
2010-06-17 06:24:37
@Rob: Fair enough. Having a covering index like that is obviously better than having just an index on `TelNumber`, or separate indexes... Nevertheless, it's still a minor argument when compared to having or not having an index on `TelNumber` (which is what makes or breaks the query). For a question that says "What is an index in table?", which I understand implies that the OP is a newcomer to databases, I still think that my answer was a good 2-line generalization of where indexes are needed.
Daniel Vassallo
2010-06-17 06:38:47
@Daniel: But if you had: `SELECT Name, TelNumber FROM users WHERE city='Valletta'`, then an index on `city` alone would be ignored in favour of a table scan, because of the impact of Lookups. I see so many people try to create indexes on single columns that are simply ignored.
Rob Farley
2010-06-17 06:51:08
@Rob: Yes, `city` could be a particular case, because an index on a low-cardinality field like `city` is rarely going to be useful... I'm sure there would be hundreds of other particular scenarios where "the general advice" doesn't apply. I only intended to address the general advice here, which is why I emphasized the answer with "very briefly" and "in general".
Daniel Vassallo
2010-06-17 07:04:04
@Daniel: No worries. Hopefully our discussion will help the asker understand a bit more about indexes as well.
Rob Farley
2010-06-17 07:06:11