Too many indexes

[jepatte]

I have a large data table with about 2.5 mil records with 60 fields. I have a web application reading this table so users can fiter, query on many fields, maybe 20 to 30 different fields. Two fields are Race and Gender, since they will be querying on these two fields, I assume I need to create an index for these searchable fields, even though the values are M/F for Gender and W/B/H/O for Race.

Otherwise it seems to take an hour or more to run a query if I didnt have the index. So my question is can I have too many indexes for something like this.

 

[AlexCuse]

You will need to be careful that your indexing strategy does not make inserts, updates, and deletes prohibitively slow.

If this is to be accessed from a web application then I would think you are more concerned with quick query execution times, so you probably won't need to worry too much about over-indexing.

Test, test, test, and test some more

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[bborissov]

In addition to Alex,
TEST!


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ptheriault]

What version of SQL is this for?

If you are on SQL 2005 here is a great query to help determine the cost of and usage of an index.

--- sys.dm_db_index_operational_stats
declare @dbid int
select @dbid = db_id()

select objectname=object_name(s.object_id), indexname=i.name, i.index_id
            , reads=range_scan_count + singleton_lookup_count
            , 'leaf_writes'=leaf_insert_count+leaf_update_count+ leaf_delete_count
            , 'leaf_page_splits' = leaf_allocation_count
            , 'nonleaf_writes'=nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count
            , 'nonleaf_page_splits' = nonleaf_allocation_count
from sys.dm_db_index_operational_stats (@dbid,NULL,NULL,NULL) s,
                        sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by reads desc, leaf_writes, nonleaf_writes
--- sys.dm_db_index_usage_stats

select objectname=object_name(s.object_id), indexname=i.name, i.index_id
                        ,reads=user_seeks + user_scans + user_lookups
                        ,writes =  user_updates
from sys.dm_db_index_usage_stats s,
            sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and s.object_id = i.object_id
and i.index_id = s.index_id
and s.database_id = @dbid
order by reads desc

go

- Paul
- If at first you don't succeed, find out if the loser gets anything.