non-unique index

raindropz · #1 (**permalink**) 04-27-11, 22:30

is it required to set non-unique index?
what is the difference between

Code:

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

and

Code:

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

healdem · #2 (**permalink**) 04-28-11, 02:16

definition of indexes depends on what methods you see of retrieving the data, by methods I mean queries. indexes are not a free lunch, there is some cost in performance on indexing a column so you shouldn't index every column.

if in your example you expect to use the inauguration date reasonably frequently (ie use it to lookup or retrieve values in a query), and there are lots of heads of state then an index may make sense. however in a realational db I'd expect there first to be a primary key on ID.

the definition looks OK, there is no requirement to specify the size of index or the sort method (ASCending or DEScending)
MySQL :: MySQL 5.1 Reference Manual :: 12.1.17 CREATE TABLE Syntax

raindropz · #3 (**permalink**) 04-28-11, 04:40

if there are many presidents inaugurated,
which query is faster?

Code:

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

or

Code:

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

thank you.

healdem · #4 (**permalink**) 04-28-11, 04:51

which query are you using?
an index is only as good as the query using it.
so if you want to find head of states called 'smith' then both table defintions are the same
if you want to find the the headofstate with an ID of 45 then likewise
if you want to find the head of state who was inaugurated on the 21 April 1960 then the second definition should be faster.

however as no query can specify the index to use, that is done by the query optimiser at run time, its academic, the index can be HINTed at in the query though.
MySQL :: MySQL 5.1 Reference Manual :: 12.2.8.2 Index Hint Syntax

so you choice of column(s) to index should reflect the expected access methods in the queries.
ferinstance if you wanted to look at the lastname you might want to use a normal index or a "full text" index depending on what you are looking for
MySQL :: MySQL 5.0 Reference Manual :: 11.9 Full-Text Search Functions

So it depends on how you think the data will be accessed or manipulated.

bear in mind however that indexing is realtively trivial to revisit during the application's development and if need be after its gone into production. you shoudl examine your queries using the SHOW or EXPLAIN options on a quedry, you shoudl examine the slow queries log. you can alwasy add or modify indexes. adding or modifying columns is a far trickier prospect.

mike_bike_kite · #5 (**permalink**) 04-28-11, 09:57

Quote:

Originally Posted by raindropz

if there are many presidents inaugurated,
which query is faster?

I'd imagine your table is going to be very small. For under a 1000 records (approx) most RDMSs would ignore the index altogether and just do a table scan no matter what indexes you specify.

raindropz · #6 (**permalink**) 04-28-11, 21:59

thank you very much

Internet Services

Web Development

Digital Marketing

Consumer Tech