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'
- should I use index only on column city
- should I use composite index on city,State & Country
- 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
- should I create index on ID field
- should I create index on City field
If you will suggest to create index on id field that reason will be
- Due to it is a bigint and bigint occupy only 4 bytes
- 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.
bigintis 8 bytes rather than 4 BTW