Multiple Index in table, advisable or not?

[atzdgreat]

Hi i have 60 columns in a table and i always used 7 columns for query. The only user who can insert update and delete in that table is the admin. The rest of user is for query only. The table holds 10k to 15k records a year. And 3 to 5 updates in a month. It took me sometime to load the data in my listview. So when i search in google that indexing column might improve the performance. I was wondering if you could enlighten me if it is advisable to index my 7 column or not. Thank you

[Micron]

Not sure what you mean/are saying. On one hand, it reads like you're asking about a 7 field composite index; on the other, some sort of index for a query that just happens to have 7 fields. Too many indexes can actually slow down performance.

Then there is the "list view" comment. Does that mean this is about SharePoint?
Maybe this will help?
https://www.fmsinc.com/free/NewTips/...ndex/index.asp

[isladogs]

Indexes speed up searches significantly but slow down action queries such as updates.
You can have separate indexes or a composite index for all 7 fields.
However certain fields will not be helped by adding indexes e.g. Yes/No or text fields with very limited values such as M/F.

I was also wondering about your listview comment. Did you mean listbox?

[atzdgreat]

Thank u both for your reply. Actually i used excel vba. And my database is access. So everytime i make a query in access the result wil display in my listview. Meaning indexing 7 columns will not helping the performance of my query... Hmmmm anyways thank you for your answer 😊😊😊

[CJ_London]

Meaning indexing 7 columns will not helping the performance of my query.

it depends on your query. Indexes should be applied to fields that you a) join on to other tables and/or b) you regularly apply criteria or filters and/or c) you regularly sort. As Colin says, there are times where indexing has no real benefit - typically records that have a very limited range of values (such as booleans) or the number of records in the table is small.

10-15k records is a pretty small number so it may be that indexes will not have a significant effect - again, depends on the query.

You've voiced a concern about the performance of your query - how long does it take to run at the moment? and can you provide the sql to the query? Just a guess but a simple query with a couple of criteria on indexed fields might take less than a second to run on a table with 15k records.

It may be the performance issue is not around the query, but the time it takes to populate Excel.