Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
2  
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
add comment

closed as too broad by Jon Seigel, Mark Storey-Smith, Nick Chammas, bluefeet, Max Vernon Sep 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.

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.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.