I've a table with 80 columns and that is a base table for most of the application. Daily load inserts almost 8,000 records and update upto 2,000 records. This table is now having more than 5 million records. Unfortunately, I can't change the architecture and for next few months I have to continue with this. Now, as I said there are multiple application connected to the table which are fetching data from it and almost all of them using different columns for their purpose. Example of few of those queries are:
SELECT Col1, Col2 FROM Table WHERE Col3 = 'something' SELECT Col4, Col5, Col6 FROM Table WHERE Col7 IN ('A','B') AND Col1 = 'something' SELECT Col1, Col2, Col7, Col28, .... FROM Table WHERE Col1 = 'Something' AND Col2 = 12 AND (Col2 > 2 OR Col7 <20)
As you can see that one column is being used in where clause and some query it is in select clause. I have created indexes and now I realized that I need more but that doesn't seems to be feasible to me as I will end up in having so many indexes.
How can I implement index strategy in this kind of scenario?
How to design index when those columns are coming up in different combinations?
SELECT Col1, Col2, Col3 FROM Table WHERE Col3 = 'something' AND Col4 = 'something' SELECT Col4,Col1, Col5 FROM table WHERE Col3='Something'AND Col7 = 'something' AND Col67 = 'something'
out of 80 columns, application is using > 50 columns in where clause.