Indexes

Question

Indexes

kensai

SSC Veteran

Points: 272
More actions
August 24, 2002 at 4:32 am

#79281

Hi. I don't know much about indexes and I'd like to ask a few questions about indexes. I'd be glad if you could answer.
1) Is there anything like having too much indexes on a table?
2) Does having an index takes space from SQL Server data file? I'm asking this because we have limited SQL Server space on our host. If yes, does it take much?
3) Think about Snitz Forum and it's database and the search page of it. On which columns would you have an index (think again about the search page please):
FORUM_REPLY table:
TOPIC_ID
REPLY_ID
R_AUTHOR
R_MESSAGE
FORUM_TOPIC table:
FORUM_ID
TOPIC_ID
T_SUBJECT
T_MESSAGE
T_AUTHOR
T_LAST_POST
T_LAST_POSTER
(I eliminated some unnecessary ones like T_VIEW_COUNT)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

dm Ten Centuries Points: 1105 More actions · Answer 1

kensai,

1) Yes, you can have too many indexes on a table. If you have a lot of insert, update and delete activity in your database, everytime you update the table the index has to be updated as well. So, if you have a transaction based system, you will want to limit your indexes to what you really need. If the data is read only a lot of indexes will not hurt select performance.

2) Yes, a index takes space in the SQL Server data file. The space used depends on the type of index (Clustered or non Clustered) and the number of columns in the index. Look up the follow topics in BOL:

"Estimating the Size of a Table"

"Estimating the Size of a Table Without a Clustered Index"

"Estimating the Size of a Table with a Clustered Index"

3) Without knowing the queries the forum uses, this question is hard to answer. My guess would be the Topic_id and reply_id would be indexed.

Hope this helps,

Diane

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 2

Like it looks like they've applied here, you'll also want to throw on some full text indexes as well since you are interested in searching. The one catch with full text indexes is that they are updated outside of SQL Server and their updates have to be scheduled as jobs (or run manually, but typically not done this way). How often to update is a good question and would be determined by processing power of the system, usage, and # of updates.

In those cases you'd want to full text index on the message and possibly the subject fields. The search engine would be built to use the full text indexes as well as the normal ones.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

kensai SSC Veteran Points: 272 More actions · Answer 3

dm:

quote:
3) Without knowing the queries the forum uses, this question is hard to answer. My guess would be the Topic_id and reply_id would be indexed.

The search has this options: Search the word for [topics|messages|both] in the selected forums.

bkelley:

I don't think I want to use full-text search. First, I had this updating job while using UBB and it's very very tedious and I don't want to do it anymore. Second, I don't know if we have a scheduled job option on server but I don't think the host will give this option to us.

Antares686 SSC Guru Points: 125444 More actions · Answer 4

One thing of note. Of the size of the index taking from the database, a clustered index can in many situations save space. The reason for this is the way data is stored without a a clustered index it creates ROW identifiers for non-clustered indexes and internal workings. I am not sure the lenght of the value but I have discovered when adding a clustered index on a good size table got smaller.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

kensai SSC Veteran Points: 272 More actions · Answer 5

Thanks for the all help.

The reason I wrote this topic was a search page which gives me a lot of headaches. I was getting a "timeout expired" error. I thought the dynamic sql was the reason so the proc is compeletly rewritten with using static sql but I'm still getting a timeout expired error. I'm thinking where I should have any index but I don't think having an index of message field for a bulletin board script is a good idea. Grrr! This is very frustrating.

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 6

If you are using Full Text, the processing occurs outside of SQL Server and Full Text is more optimized for searches of this sort. I know you have an aversion to Full Text, but you'll find an example of its use with Microsoft's Support site. It's more geared to the searches you are trying to do.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

kensai SSC Veteran Points: 272 More actions · Answer 7

Ok bkelley I'll check it out. Maybe it'll do more good for me.

I just don't get it that Snitz does this working with a more complex sql string than mine on an Access database and I'm having a timeout error using a stored procedure on a SQL Server :/