SQL Anywhere database - Redundant index engine print

Author

Redundant index engine print

Russell J Morgan

2005-07-21, 11:24 am

dbeng9 -v 9.0.2.2451

Hi All

Database engine output, Is this message important:

Performance warning: Redundant index "Qualcode Index on Code" for table
"QUALCODE" in database "nlcore"

TIA

Russell Morgan

Breck Carter [TeamSybase]

2005-07-21, 1:23 pm

On 21 Jul 2005 09:09:47 -0700, "Russell J Morgan"
<russell.morgan@careervision.co.uk> wrote:

>dbeng9 -v 9.0.2.2451
>
>Hi All
>
>Database engine output, Is this message important:

One redundant index is probably not important in the grand scheme of
things.

There are two indexes on the same columns; this wastes the space
required to store the second index, and wastes some time when
inserting and deleting rows (and updating, if you change index column
values).

>Performance warning: Redundant index "Qualcode Index on Code" for table
>"QUALCODE" in database "nlcore"
>
>TIA
>
>
>Russell Morgan
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Luis

2005-07-21, 8:23 pm

I have similar problem when I upgrade from 5 to 9... we found that we were
doing index for field that are primary key (primary key contains internal
index)... the result is that there are 2 indexes for the same columns... it
also happend when you create index for unique fields... (it seems to also
have some kind of internal index)

Create Table table1
{ ID integer default autoincrement,
OfherField Integer unique,

primary key(id)
};

Create Index MyIndexfortable1 (ID); <--- duplicate index because of primary
key
Create Index MyOtherIndexfortable
1 (OtherField); <--- duplicate index
because of unique constrain

Luis

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:numvd1hksdmuk69
2hvnve61qjtpuf2evve@
4ax.com...
> On 21 Jul 2005 09:09:47 -0700, "Russell J Morgan"
> <russell.morgan@careervision.co.uk> wrote:
>
>
> One redundant index is probably not important in the grand scheme of
> things.
>
> There are two indexes on the same columns; this wastes the space
> required to store the second index, and wastes some time when
> inserting and deleting rows (and updating, if you change index column
> values).
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Luis

2005-07-21, 8:23 pm

Actually here is some info from sybase site...

http://www.sybase.com/detail?id=1032350

Luis

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:numvd1hksdmuk69
2hvnve61qjtpuf2evve@
4ax.com...
> On 21 Jul 2005 09:09:47 -0700, "Russell J Morgan"
> <russell.morgan@careervision.co.uk> wrote:
>
>
> One redundant index is probably not important in the grand scheme of
> things.
>
> There are two indexes on the same columns; this wastes the space
> required to store the second index, and wastes some time when
> inserting and deleting rows (and updating, if you change index column
> values).
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com

Breck Carter [TeamSybase]

2005-07-21, 8:23 pm

You always had the duplicate indexes, now you're getting told about
them :)

Indexes are automatically generated for all primary key, foreign key
and unique constraints.

Breck

On 21 Jul 2005 12:13:34 -0700, "Luis" <Luis@noemail.com> wrote:

>
>I have similar problem when I upgrade from 5 to 9... we found that we were
>doing index for field that are primary key (primary key contains internal
>index)... the result is that there are 2 indexes for the same columns... it
>also happend when you create index for unique fields... (it seems to also
>have some kind of internal index)
>
>Create Table table1
>{ ID integer default autoincrement,
> OfherField Integer unique,
>
>primary key(id)
>};
>
>Create Index MyIndexfortable1 (ID); <--- duplicate index because of primary
>key
>Create Index MyOtherIndexfortable
1 (OtherField); <--- duplicate index
>because of unique constrain
>
>Luis
>
>"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
>message news:numvd1hksdmuk69
2hvnve61qjtpuf2evve@
4ax.com...
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Russell J Morgan

2005-07-22, 7:23 am

Thanks all, very helpful!

Sponsored Links