hsteinestel@web.de said:
>
> Hello,
>
> we have a database table with 7 columns and approximately 10 million
> rows. There is a unique index on 6 columns. The customer with the 10
> million rows had problems with his system because the INSERTs were very
> slow. Yesterday, his database administrators added 2 new indizes.. Now,
> the perfomance is much better.
>
> Why??
>
> I always thought a unique index is the best way to optimize the DB.

A unique index across SIX
COLUMNS??????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????

--

Bye now,
Obnoxio

"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche

A smile is a gift that is free to the giver and precious to the recipient.
But giving someone the finger is free too, and I find it more personal and
sincere.
sending to informix-list

Yes. What's wrong with that?

Obnoxio The Clown schrieb:
> hsteinestel@web.de said:
> >
> > Hello,
> >
> > we have a database table with 7 columns and approximately 10 million
> > rows. There is a unique index on 6 columns. The customer with the 10
> > million rows had problems with his system because the INSERTs were very
> > slow. Yesterday, his database administrators added 2 new indizes.. Now,
> > the perfomance is much better.
> >
> > Why??
> >
> > I always thought a unique index is the best way to optimize the DB.
>
> A unique index across SIX
> COLUMNS??????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????
>
> --
>
> Bye now,
> Obnoxio
>
> "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
> - Coluche
>
> A smile is a gift that is free to the giver and precious to the recipient.
> But giving someone the finger is free too, and I find it more personal and
> sincere.
> sending to informix-list

the fastway to access data can be to have indices depending on the
selectivity of the queries

the fastest way to insert data should be to have no indexes!! It is
only 1 write at the end of the data set. if there is an index as well
then you have to write the row at the end of the data set and add the
row to the index.

so thats 2 writes and a load of reads to decide where in the index the
write needs to be.

if the index tree becomes very long it can take a long time to insert
data as the tree has to be traversed to see where in the index the
pointer to the row needs to be added as well as adding the row itself.

You say they added 2 new indices. did they drop (and maybe recreate)
the existing index as this may have "rebalanced" the existing index
causing the performance to improve.





Holger Steinestel wrote:
> Yes. What's wrong with that?
>
> Obnoxio The Clown schrieb:
> > hsteinestel@web.de said:
> > >
> > > Hello,
> > >
> > > we have a database table with 7 columns and approximately 10 million
> > > rows. There is a unique index on 6 columns. The customer with the 10
> > > million rows had problems with his system because the INSERTs were very
> > > slow. Yesterday, his database administrators added 2 new indizes.. Now,
> > > the perfomance is much better.
> > >
> > > Why??
> > >
> > > I always thought a unique index is the best way to optimize the DB.
> >
> > A unique index across SIX
> > COLUMNS??????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????
> >
> > --
> >
> > Bye now,
> > Obnoxio
> >
> > "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
> > - Coluche
> >
> > A smile is a gift that is free to the giver and precious to the recipient.
> > But giving someone the finger is free too, and I find it more personal and
> > sincere.
> > sending to informix-list

You are right, the first attempt was to recreate the old index. But
that did not improve the performance.

Is it possible that the 2 new indices help the INSERT statement to
check the unique key a lot faster?

scottishpoet schrieb:
> the fastway to access data can be to have indices depending on the
> selectivity of the queries
>
> the fastest way to insert data should be to have no indexes!! It is
> only 1 write at the end of the data set. if there is an index as well
> then you have to write the row at the end of the data set and add the
> row to the index.
>
> so thats 2 writes and a load of reads to decide where in the index the
> write needs to be.
>
> if the index tree becomes very long it can take a long time to insert
> data as the tree has to be traversed to see where in the index the
> pointer to the row needs to be added as well as adding the row itself.
>
> You say they added 2 new indices. did they drop (and maybe recreate)
> the existing index as this may have "rebalanced" the existing index
> causing the performance to improve.
>
>
>
>
>
> Holger Steinestel wrote:
> > Yes. What's wrong with that?
> >
> > Obnoxio The Clown schrieb:
> > > hsteinestel@web.de said:
> > > >
> > > > Hello,
> > > >
> > > > we have a database table with 7 columns and approximately 10 million
> > > > rows. There is a unique index on 6 columns. The customer with the 10
> > > > million rows had problems with his system because the INSERTs were very
> > > > slow. Yesterday, his database administrators added 2 new indizes.. Now,
> > > > the perfomance is much better.
> > > >
> > > > Why??
> > > >
> > > > I always thought a unique index is the best way to optimize the DB.
> > >
> > > A unique index across SIX
> > > COLUMNS??????????????????????????????????????????? ?????????????????????????????????????????????????? ?????????????????????????????????????
> > >
> > > --
> > >
> > > Bye now,
> > > Obnoxio
> > >
> > > "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
> > > - Coluche
> > >
> > > A smile is a gift that is free to the giver and precious to the recipient.
> > > But giving someone the finger is free too, and I find it more personal and
> > > sincere.
> > > sending to informix-list

well, it depends what actually makes the data unique

if 3 columns out of the 6 actually make the data unique and these were
not the first 3 columns of the original index then yes it would make it
much quicker to check

Obvioulsy I don't know the details of the columns and data involved in
the original index and the resulting 2 indices.

Hello,

this is the definition of the table:

create table "admin".table1
(
col1 integer not null ,
col2 char(8) not null ,
col3 date not null ,
col4 integer,
col5 char(2) not null ,
col6 smallfloat,
col7 char(1) not null
);
revoke all on "admin".table1 from "public";

and here the OLD indices:

create unique index "admin".i_table1_1 on "admin".table1
(col1,col2,col3,col4,col5,col7);
create index "admin".i_table1_2 on "admin".table1
(col1,col2,col3,col7);

There was already a second index which I did not mention, sorry for
that. I do not have the exact definitions of the new indices, but I
remember that it used the field col6.



scottishpoet schrieb:
> well, it depends what actually makes the data unique
>
> if 3 columns out of the 6 actually make the data unique and these were
> not the first 3 columns of the original index then yes it would make it
> much quicker to check
>
> Obvioulsy I don't know the details of the columns and data involved in
> the original index and the resulting 2 indices.

col6 isn;t in the old index decinfitions at all.

so quite probably they have found a better way to declare the uniquness
using a much smaller index which is easier to travers

I'll be back tomorrow with the new indices.

Our business logic makes the first unique index necessary. The other
one speeds up some reports and lists, I think. But I still do not
understand why adding two new indices makes an INSERT faster.


scottishpoet schrieb:
> col6 isn;t in the old index decinfitions at all.
>
> so quite probably they have found a better way to declare the uniquness
> using a much smaller index which is easier to travers

if the index has "more unique" rows in the first column it means less
reads need ot be made and more data can be discarded more quickly

when we do an insert you need to traverse the index to work out where
in the index where the index data needs to go.

Also, you'll get the actual data for those columns in the index ( so if
you only query on that columns in that index you don;t read the actual
table). If you have 6 colmns in the index out of a 7 column table then
you almost have a complete copy of the table in the index and may have
to travers a large amount of it to decide wher the row goes.