I have a table with fields as follows:
* sag_id
* sag_header
* sag_comments
* sag_date
* sag_section_id (**)
* sag_artist_id (**)
* sag_author_id (**)
* sag_producer_id (**)
As you can see, fields mark with (**) are identifiers that reference another table (catalog of sections, catalog of artists, etc). Firstly, I need an index on "sag_date" because our application has a search option under this criteria. However, there are also search criterias on "sag_section", "sag_artist", "sag_author" and "sag_producer" because, for example, a user may need to get the records of certain artist only. Furthermore, our application offers a user to select several cominations of criterias:
* Artist + Author
* Artist + Producer
* Artist + Author + Producer
* Section + Artist.
And so on. What I see is that it is not a good decision to set a key for every possibility because it will have an impact on performance due to index maintenance. What would be a good way to define indexes in a case like this?
With respect,
Jorge Maldonado