5 ms·

How do you create the correct indexes? If we create index for all the conditions in a where clause wouldn’t the number of indexes become too many? I don’t fully

by hi41 2y ago

How do you create the correct indexes? If we create index for all the conditions in a where clause wouldn’t the number of indexes become too many? I don’t fully grasp how to best create indexes. Does anyone have good reference and examples?

beart 2y ago
Creating good indexes is a nuanced problem to solve. It is highly dependent on the primary use case of the data. For instance, if you have a table representing varieties of beer, you are likely to present an interface for searching by brand, or type (ipa, pilsner). Those would be good targets for indexes. However, you probably won't often search by color, so it won't be worth indexing that column. Typically I would add any indexes initially that I know will be heavy hitters, then refine as needed based on actual use. Most databases provide tools to help identify missing indexes for common queries.
- valiant55 2y ago
  I think a good baseline is just include indexes for foreign key relationships and then adjust from there. Also, if anyone reading this is using SQL Sever, do not use the recommended missing indexes it provides. They have a whole host of issues, including the ordering of columns and straight up recommend completely unnecessary indexes.
  - hi41 2y ago
    Yes, we are using MS SQL. Will keep your advice in my mind.
golergka 2y ago
Mostly where EXPLAIN ANALYZE points to.
mike_hearn 2y ago
Some databases can do it autonomously. Oracle is an example. It can notice if you keep doing inefficient queries and add the needed indexes by itself.
consteval 2y ago
Depends entirely on your application's data access patterns. What columns is your application actually using to do retrieval and insert?
magicalhippo 2y ago
Run the database profiler in production, logging queries which queries take a long time (ie >200 ms say). Queries that come up repeatedly should be looked at. Use the query analyzer to figure out where it's doing table scans or similar, and you'll probably quickly see which index you need to create. Keep in mind the database server tries to use indexes to minimize the amount of rows it needs to scan. So you just need enough to drastically cut down the number of rows to consider. If you have five columns in the where clause, think of which one or which combination is the most specific. This also means if you have a query with a where clause "colX = 1", yet 97% of the rows have colX equal to 1, adding and index on colX won't help. This means it can be customer-specific. Also keep in mind that some database servers don't index null values, thus "where colX is null" means a table scan regardless. In such cases an alternative could be to add a computed column which would have a definite value, say "coalesce(colX, -1)" if colX should inly have positive values. You can then index on that, and tweak your queries to filter on the computed column. Also keep in mind that if you have "order by", then the database server might ignore your index if it won't help in presenting the rows in the order dictated by the "order by" clause. We have some tables where we have multiple permutations of the same columns indexed due to this. It's a skill and it's very database server dependent, just like learning to know the optimizer in your programming language of choice. Takes a fair bit of trial and error using the query analyzer.

by hi41 2y ago

beart 2y ago

Creating good indexes is a nuanced problem to solve. It is highly dependent on the primary use case of the data. For instance, if you have a table representing varieties of beer, you are likely to present an interface for searching by brand, or type (ipa, pilsner). Those would be good targets for indexes. However, you probably won't often search by color, so it won't be worth indexing that column. Typically I would add any indexes initially that I know will be heavy hitters, then refine as needed based on actual use. Most databases provide tools to help identify missing indexes for common queries.

valiant55 2y ago
I think a good baseline is just include indexes for foreign key relationships and then adjust from there. Also, if anyone reading this is using SQL Sever, do not use the recommended missing indexes it provides. They have a whole host of issues, including the ordering of columns and straight up recommend completely unnecessary indexes.
- hi41 2y ago
  Yes, we are using MS SQL. Will keep your advice in my mind.

golergka 2y ago

Mostly where EXPLAIN ANALYZE points to.

mike_hearn 2y ago

Some databases can do it autonomously. Oracle is an example. It can notice if you keep doing inefficient queries and add the needed indexes by itself.

consteval 2y ago

Depends entirely on your application's data access patterns. What columns is your application actually using to do retrieval and insert?

magicalhippo 2y ago

Run the database profiler in production, logging queries which queries take a long time (ie >200 ms say). Queries that come up repeatedly should be looked at. Use the query analyzer to figure out where it's doing table scans or similar, and you'll probably quickly see which index you need to create. Keep in mind the database server tries to use indexes to minimize the amount of rows it needs to scan. So you just need enough to drastically cut down the number of rows to consider. If you have five columns in the where clause, think of which one or which combination is the most specific. This also means if you have a query with a where clause "colX = 1", yet 97% of the rows have colX equal to 1, adding and index on colX won't help. This means it can be customer-specific. Also keep in mind that some database servers don't index null values, thus "where colX is null" means a table scan regardless. In such cases an alternative could be to add a computed column which would have a definite value, say "coalesce(colX, -1)" if colX should inly have positive values. You can then index on that, and tweak your queries to filter on the computed column. Also keep in mind that if you have "order by", then the database server might ignore your index if it won't help in presenting the rows in the order dictated by the "order by" clause. We have some tables where we have multiple permutations of the same columns indexed due to this. It's a skill and it's very database server dependent, just like learning to know the optimizer in your programming language of choice. Takes a fair bit of trial and error using the query analyzer.