Take the following two tables:
table Ahas ~100k rows and 50 columns, and it is being updated only twice per month, how long this update takes is not an issue (as long as it doesn't take more than one day, which I think is unlikely :D). It will grow very slightly, not more than 10k rows per year.
table Bhas ~25k rows and 25 columns,
column Fis a foreign key to
table A. This table is being updated 100-1000 times a day, most of the time to add new rows, so it will easily grow to hundreds of thousands of rows.
Table A has a one-to-many relation to
table B, both
column F and
column C must be not null, have their own index, and
column C is also unique but it is not the primary key of
table A. Every row of
table B has a unique correspondence with a row in
Both tables are being queried many thousands of times every day, individually or using their relation using an application that allows a few dozen different types of query.
Is it a good idea to index every column of
table Athat is involved in a query, plus making a composite index for every type of query? If no, what are the possible drawbacks?
table Bis it a good idea to make a composite index only for the most time consuming queries? Or should I make a composite index also for every column in
table Bthat is being queried, since those queries may vary in the parameters but not in the form?