Hello all --

I have a table with two fields. They are indexed and also have an
index to be unique. When I look at the table in phpMyAdmin, it says
that the two indexes appear to be identical.

I have the index to query speed, and I have the unique for data
integrity. If I have them simply set as unique, does that get me the
query speed-up that a regular index would have?

On Tue, 12 Feb 2008 16:38:07 +0100, <lawpoop@gmail.com> wrote:

> Hello all --
>
> I have a table with two fields. They are indexed and also have an
> index to be unique. When I look at the table in phpMyAdmin, it says
> that the two indexes appear to be identical.
>
> I have the index to query speed, and I have the unique for data
> integrity. If I have them simply set as unique, does that get me the
> query speed-up that a regular index would have?

Yes, on SELECT it would.
--
Rik Wasmus

lawpoop@gmail.com wrote:
> Hello all --
>
> I have a table with two fields. They are indexed and also have an
> index to be unique. When I look at the table in phpMyAdmin, it says
> that the two indexes appear to be identical.
>
> I have the index to query speed, and I have the unique for data
> integrity. If I have them simply set as unique, does that get me the
> query speed-up that a regular index would have?
To answer the question in the subject line -- only if they use the same
order and number of column segments...

You can have a Non-unique index as well as unique index on the same
table - but not the same columns/order as the unique index will ensure
no other values are added to the table and therefore the non-unique
index- at this point is redundant.

Example: this will only allow a unique value for col1 making the second
index redundant and therefore unnecessary:
UNIQUE INDEX (col1)
INDEX (col1,col2,col3)

Values:
Col1 Col2
a b
b b
c c
.... in this example you could never have say: col1=a,col2=c because of
the unique index on Col1.

Now what happens if your INDEX is (col2,col1...colx)??? - It is all
about knowing your data and knowing your application and how it will use
the data.

Testing can be your best teacher... You would also do well to spend time
on the net or in other books on database technologies, what makes them
tick if you are going to be spending any time trying to design a
database. This is in no way meant to disparage or dissuade you from
asking questions or continuing to develop, but to encourage more than
just a command-line or query language knowledge of the tool in which you
wield. With databases, just remember, just because you can, doesn't mean
you should.

[WAR-STORY]
I used one database engine where they had a HASHED-key (bit-map-sort-of)
index and a UNIQUE SORTED-key (b-tree) index that were defined the same
unique values - if you were doing a direct lookup ("where a = 'abc';")
the HASHED index lookup was blazingly fast, but that same HASHED index
would perform very poorly (full index scan) for range results ("where a
between 'abc' and 'fgh' MySQL isn't quite that sophisticated yet, but
you get the idea that in this particular case these two types of indexes
performed two very different functions while also preserving data
integrity.