# I have to create INDEX but which? [closed]

I have to create INDEX but which with this query ?

``````SELECT *
FROM TABLE
WHERE A = 1
AND B = '5427dbfac0a8280d74bc672c72636a4f'
AND C = 'BIL'
AND D = 'B'
and E = '01'
GROUP BY F,G,E,H,I
ORDER BY B,C,E,G,H,I;
``````
-

## closed as too broad by Jon Seigel, Mark Storey-Smith, Nick Chammas, bluefeet♦, Max VernonSep 30 '13 at 19:48

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

It very much depends on the balance of data in each of those columns with respect to the filtering clauses. We can not hope to approach an optimal answer with as little information as is presented. – David Spillett Sep 30 '13 at 17:21

## 1 Answer

• The `B`, `C` and `E` columns will all have constant values as they are included in the `WHERE` clause, so there is no need to be in the `GROUP BY` and `ORDER BY` clauses. Therefore, the `ORDER BY` can be written as `ORDER BY G,H,I`

• Since you can always rearrange columns in the `GROUP BY` clause without altering the results, that can be written as `GROUP BY G,H,I,F` to be aligned with the `ORDER BY` clause.

• No reason to have all the columns in the `SELECT` list - especially those that are limited by the `WHERE` clause. Keep only those needed.

The query now becomes:

``````SELECT F,G,H,I     -- with possibly other columns, as needed
FROM TABLE
WHERE A = 1
AND B = '5427dbfac0a8280d74bc672c72636a4f'
AND C = 'BIL'
AND D = 'B'
and E = '01'
GROUP BY G,H,I,F
ORDER BY G,H,I ;
``````

and possible indexes that could help:

• `(A)` or `(B)` or `(C)` or `(D)` or `(E)` to limit the rows. This could have tiny or huge effect on efficiency, depending on the selectivity of the column.

• `(A,B)` or `(C,A)` or .... or `(A,C,D,E,B)` i.e. whatever combination of the 5 columns in WHERE or all them (order doesn't matter). This should be better than the previous single-column indexes as the rows need to be returned will be found via the index. The result will still have to be grouped and ordered though.

• `(A,B,C,D,E, G,H,I,F)` Even better as the whole query (selecting, grouping and ordering) can be done with an index scan. If you don't have any more columns in the `SELECT` list, this is the best index you can have.

• `(A,B,C,D,E, G,H,I,F, <moreColumns>)` If you do have more columns in the `SELECT` list, you can include them in the index.

Now, which index to use really depends on lot of factors like:

• Is this query crucial? How often is it run and how fast does it need to run?

• How many rows does the `WHERE` clause limit the query to? If they are not many, you may just be ok with the `(A,B,C,D,E)` index or even narrower.

• How many rows does the `GROUP BY` limit the query to and are there calculations in the `SELECT` list (like `MAX(K)`)?

• Can any of these indexes help other queries as well? Indexes with many columns are useful for very few queries and add overhead for the write operations on the table so you wouldn't want to create too many of them.

-