Do I need INDEXes when a UNIQUE has been set?

[Laphan]

Having re-jigged the UNIQUE to the proper syntax it does do what I want -
wahey!!

Just to finish this off here is my final table DDL:

CREATE TABLE `WEBSTRINGS` (
`STRINGID` INT NOT NULL AUTO_INCREMENT,
`TOKENID` INT DEFAULT 0,
`LANGID` VARCHAR(30),
`STRINGTEXT` VARCHAR(255),
`PAGEID` TINYINT UNSIGNED DEFAULT 0,
`GUI` TINYINT UNSIGNED DEFAULT 0,

INDEX `indxLANGID` (`LANGID`),
INDEX `indxGUI` (`GUI`),
INDEX `indxTOKENID` (`TOKENID`),
INDEX `indxSTRINGID` (`STRINGID`),
UNIQUE `unqSTRINGID` (`LANGID`,`STRINGTEXT`,`GUI`),
PRIMARY KEY `PrimaryKey` (`STRINGID`)
);

Do I really need the other INDEXes if I have this UNIQUE one set (and a PK)
?

What would be the pluses and minuses of keeping the indexes in realtion to
my ASP page work?

Thanks

Laphan

[Aaron [SQL Server MVP]]

> Do I really need the other INDEXes if I have this UNIQUE one set (and a

> PK)
> ?

That really depends on your goal, and how your database implements a UNIQUE
constraint on a column.

In SQL Server, this would be redundant, since a unique constraint is
implemented as an index.

However, since you use the word AUTO_INCREMENT, I don't think this is SQL
Server at all. Have you considered asking in a forum dedicated to your
database platform?

> What would be the pluses and minuses of keeping the indexes in realtion to
> my ASP page work?

In general, indexes:

- speed up sorting, bookmark lookups, and joins
- slow down inserts

With uniques in particular, they help to keep redundant data out of your
database.

So, as I said before, the "pluses and minuses" really depend on your
objective(s).

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Astra]

Hi Aaron

Thanks for coming back to me.

What I'm trying to understand is that isn't the unique already doing the job
of the index, so do I really need to create a separate index as well for
each of the fields/columns?

Rgds Robbie

"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:OFNTSg4cEHA.1048@tk2msftngp13.phx.gbl...

> Do I really need the other INDEXes if I have this UNIQUE one set (and a
> PK)
> ?

That really depends on your goal, and how your database implements a UNIQUE
constraint on a column.

In SQL Server, this would be redundant, since a unique constraint is
implemented as an index.

However, since you use the word AUTO_INCREMENT, I don't think this is SQL
Server at all. Have you considered asking in a forum dedicated to your
database platform?

> What would be the pluses and minuses of keeping the indexes in realtion to
> my ASP page work?

In general, indexes:

- speed up sorting, bookmark lookups, and joins
- slow down inserts

With uniques in particular, they help to keep redundant data out of your
database.

So, as I said before, the "pluses and minuses" really depend on your
objective(s).

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Aaron [SQL Server MVP]]

> What I'm trying to understand is that isn't the unique already doing the
job

> of the index, so do I really need to create a separate index as well for
> each of the fields/columns?

I thought I addressed that already:

"In SQL Server, this would be redundant, since a unique constraint is
implemented as an index."

I'm assuming SQL Server, but you still didn't tell us which database you're
using.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Laphan]

Hi Aaron

It's a MySQL 4 DB.

Rgds Robbie

Aaron [SQL Server MVP] <ten.xoc@dnartreb.noraa> wrote in message
news:#Y7TzmKdEHA.3632@TK2MSFTNGP09.phx.gbl...

> What I'm trying to understand is that isn't the unique already doing the

job

> of the index, so do I really need to create a separate index as well for
> each of the fields/columns?

I thought I addressed that already:

"In SQL Server, this would be redundant, since a unique constraint is
implemented as an index."

I'm assuming SQL Server, but you still didn't tell us which database you're
using.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Aaron [SQL Server MVP]]

Then you'll have to consult the MySQL folks. I've played with it but not
enough to know how they implement UNIQUE CONSTRAINTs.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)





"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:41086723_3@127.0.0.1...

> Hi Aaron
>
> It's a MySQL 4 DB.
>
> Rgds Robbie