non-unique index

[raindropz12]

is it required to set non-unique index?

what is the difference between

create table headofstate{
id int not null,
lastname varchar(30) not null,
inauguration date not null
);

 

and

create table headofstate{
id int not null,
lastname varchar(30) not null,
inauguration date not null,
INDEX(inauguration)
);

[Muddy_Funster]

I would worry less about non-unique indexes and more about Primary Key indexes.

 

Indexes in databases do what they do everywhere else - make the process of finding things much quicker and easier IF they are used properly.  A non-unique index allows for multiple entries of the same value to be included within the indexed field (not something I think you would need in the given scenrio unless multiple preasidents were inaugurated on the same date...)

[raindropz12]

if there are many presidents inaugurated,

which query is faster?

 

create table headofstate{
id int not null,
lastname varchar(30) not null,
inauguration date not null
);

 

or

 

create table headofstate{
id int not null,
lastname varchar(30) not null,
inauguration date not null,
INDEX(inauguration)
);

thank you.

[Muddy_Funster]

neither of those are queries 

 

It would depend on how you are quering the data, I would expect that you would actualy want to index the last name field, and of cource set ID to a primary key.  but as I said, as no two preasidents are going to have the same inauguration date, you would use a unique index for that field if any at all.

 

And to be honest, for all the preasidents there have been, you would only be talking a difference of milliseconds between each option.

[raindropz12]

thank  you