Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2011
    Posts
    14

    Unanswered: non-unique index

    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)
    );

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2011
    Posts
    14
    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.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by raindropz View Post
    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.

  6. #6
    Join Date
    Apr 2011
    Posts
    14
    thank you very much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •