side-effects of indexing 
Author Message
 side-effects of indexing
Could someone tell me what are side-effects of indexing some fields of
tables in a SQL database?  Thanks a lot!


Sat, 09 Oct 2004 03:16:33 GMT
 side-effects of indexing

bessie,

the sideeffect of indexing is generally, that you will have a bit
more processing on each insert, update or delete statement on that
table, because the index is to be updated appropriatly, also.
cheers

     robert



Sat, 09 Oct 2004 03:29:11 GMT
 side-effects of indexing
Hi

Yes, This is true

Another Side Effect is that more space is allocated in the database to
accomodate the index.

It is a good idea to carefully consider which columns should be indexed
(which columns will often be used in queries) and just index those. Also it
is not usually very helpful to index a column which has only a few unique
values. An example of this would be a table with a million rows that has a
field called Gender which has a value of "M" or "F".

-Dick


Quote:
> bessie,

> the sideeffect of indexing is generally, that you will have a bit
> more processing on each insert, update or delete statement on that
> table, because the index is to be updated appropriatly, also.
> cheers

>      robert



Sat, 09 Oct 2004 19:28:09 GMT
 side-effects of indexing
Just wondering, why is it not a good idea to index a column with only
a few unique values? say i have 10000 records, if theres a gender
field and i want to return all the males, without index it would have
to go thru all 10000 records just to return me half of it or howeve
many male there are.
Quote:

> Hi

> Yes, This is true

> Another Side Effect is that more space is allocated in the database to
> accomodate the index.

> It is a good idea to carefully consider which columns should be indexed
> (which columns will often be used in queries) and just index those. Also it
> is not usually very helpful to index a column which has only a few unique
> values. An example of this would be a table with a million rows that has a
> field called Gender which has a value of "M" or "F".

> -Dick



> > bessie,

> > the sideeffect of indexing is generally, that you will have a bit
> > more processing on each insert, update or delete statement on that
> > table, because the index is to be updated appropriatly, also.
> > cheers

> >      robert



Sun, 10 Oct 2004 13:06:20 GMT
 side-effects of indexing
Borge,

  If the index on gender is non-clustered, using an index scan means
the index will be scanned for all the male entries, and that will only
provide the clustered index keys (if there is a clustered index) or the
physical heap locations (if there is no clustered index) of the rows you
want.  Those rows will then have to be found in the clustered index
(which might involve a lot of page faults, since they are not contiguous)
or in the heap (same thing).

Instead of bookmarking 5000 clustered index values and then having to
find all these rows, starting at the top level of the clustered index, it will
be faster simply to scan the entire table.

If the query that returns all the males returns only a few columns from a
table with many columns, an index on all these columns (including gender)
could be helpful, since it will "cover" the query, and all the information needed
is in the index, avoiding the time-consuming lookups in the clustered index
or heap.

Steve Kass
Drew University

Quote:

> Just wondering, why is it not a good idea to index a column with only
> a few unique values? say i have 10000 records, if theres a gender
> field and i want to return all the males, without index it would have
> to go thru all 10000 records just to return me half of it or howeve
> many male there are.


> > Hi

> > Yes, This is true

> > Another Side Effect is that more space is allocated in the database to
> > accomodate the index.

> > It is a good idea to carefully consider which columns should be indexed
> > (which columns will often be used in queries) and just index those. Also it
> > is not usually very helpful to index a column which has only a few unique
> > values. An example of this would be a table with a million rows that has a
> > field called Gender which has a value of "M" or "F".

> > -Dick



> > > bessie,

> > > the sideeffect of indexing is generally, that you will have a bit
> > > more processing on each insert, update or delete statement on that
> > > table, because the index is to be updated appropriatly, also.
> > > cheers

> > >      robert



Sun, 10 Oct 2004 20:00:05 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Replication Side-effects

2. User-defined Functions and Side-effects

3. strange UPDATE side-effect

4. Side Effects of dropping/Recreating a big Table

5. interesting side effect of autocommit = off

6. Bad side-effect from making EXPLAIN return a select result

7. Batchmove side effects

8. Side effects of Partitioning

9. Side Effects of Setting Tables Memory Resident

10. MDAC Side-effects

11. Another dodgy side effect of Patch 7902/7983...

12. side effects of resize?


 
Powered by phpBB® Forum Software