Composite/Combined Indexing vs Single Index vs multiple Combined Indexed on a large table

Question

I have a very large table (that is still growing) (around 90GB with ~350mil rows). It is a table that include sales of items, if you were wondering.

In this table, there's (for example), column A,B,C,D,E,F,G. Currently, I am using a combined index, consisting of (A,B,C,D,E,F).

Usually, the query will consist of A,B,C,D,E. F is included occasionally (hence the indexing). eg,

SELECT * FROM table WHERE A = ? AND B = ? AND C = ? AND D = ? AND E = ?;

Sometimes, with the addon of AND F = ?;

But on certain occasion, the query will consist of A,B,C,D,G (whereby G is not indexed (not combined nor single indexed).

This causes timeout on certain occasion as the data is quite big.

So my question is, in order to solve this issue in terms of indexing,

should I

Option 1: add G into the combined index, making it become (A,B,C,D,E,F,G).

• Does this even work when I query A,B,C,D,G (missing E & F)?

Option 2: add G as a single index.

• Based on what i know, this does not work, as my query has A,B,C,D,G. The first combined index will be used instead (correct me if I'm wrong).

Option 3: Go with option 1, combine all the columns, but I change my query instead, to always query A,B,C,D,E,F,G even when F is not needed.
eg,

SELECT * FROM table WHERE A = ? AND B = ? AND C = ? AND D = ? AND E = ? AND F IS NOT NULL AND G = ?;

Thanks

Answer 1

Option 1 - Yes, this will work. The server will perform index seek by (A,B,C,D,E) and furter index scan by (G).

Option 2 - Makes no sense in most cases, server uses only one index for one source table copy. But when the selectivity of single index by (G) is higher than one for (A,B,C,D,E) combination then the server will use this single-column index.

Option 3 - The processing is equal to one in Option 2.