Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table with following 3 fields

1) Id      bigint
2) Name    varchar(100)
2) Country varchar(50)
3) State   varchar(50)
4) City    varchar(50)

In a country there are no. of states and in a state there are no. of city

I have two queries

a) If I want to know name of persons who are living in a city='Nagpur'

  1. should I use index only on column city
  2. should I use composite index on city,State & Country
  3. should I use composite index on country, state & city

b) If I know that person with ID 1 to 100 are in Nagpur, for such case

  1. should I create index on ID field
  2. should I create index on City field

If you will suggest to create index on id field that reason will be

  1. Due to it is a bigint and bigint occupy only 4 bytes
  2. Id field is unique so it has more selectivity, so even if output of both query will be same Index on Id will give faster respnose.
share|improve this question
4  
Normalize your data prior of thinking of indexes –  Oleg Dok Jan 18 '12 at 11:33
    
bigint is 8 bytes rather than 4 BTW –  Martin Smith Jan 18 '12 at 11:36
    
....And that's 5 fields but you have two 2 fields... –  JNK Jan 18 '12 at 12:51
add comment

1 Answer 1

As Oleg mentions, you really should normalize. For your scenarios, though:

A - I would use an index on City INCLUDE ([name]) - you don't need to sort by name as well but you do want that field at the leaf level of the index.

B - I'm not sure what you are asking here. If you have an auto-incrementing ID then that should be your clustered index.

There's a lot of reasons to use an auto-incrementing ID as your clustered key. I won't go in depth on these (there are a ton of resources, and Kim Tripp is a really good place to start):

  • Will only insert new records at the end of the index
  • Narrow (since it will be a part of every other index)
  • Unique (keeps it narrow since non-unique indexes need a "uniqueifier" additional int added)
  • Non-nullable
share|improve this answer
    
But in this case if id field is not incremental or primary key than what should I do –  Ashish Khandelwal Jan 18 '12 at 13:22
1  
Why do you even have an id field then? If you're not normalizing, and not using it as a PK, then what purpose does it serve? –  JNK Jan 18 '12 at 13:34
    
It is not like that I not used Id field as primary key in my table. I just want to clear my doubts that if there is any other field as primary field than whom should i use in index –  Ashish Khandelwal Jan 19 '12 at 4:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.