10

We have a table with columns

 ID
 OtherTableId
 DataA
 DataB
 DataC
 DataD

Does it make sense to create index on OtherTableID INCLUDE (ID, DataA, DataB, DataC, DataD) when we need all columns, or is it enough to create index just on (OtherTableID)?

I know that index with include will effectively duplicate table size, but will it give performance improvement?

Query is:

select * from Table Where OtherTableID = ?
0

3 Answers 3

6

If

  • your query uses all those fields,
  • the sort order needs to be different than that of the clustered index, AND
  • you can't change the clustered index,

an additional (covering) index with a different column order may work for you. As with anything index-related, more (and wider) indexes means slower write/update performance. This is particularly true if you're effectively doubling the size of the table.

If you just index OtherTableId without including the other columns, you'll get a smaller index but your query may end up either just using the clustered index with expensive Scan or Sort operators, or using Key Lookups from the non-clustered index, which may be great if you're querying just a few rows at a time but disastrous if you're scanning the entire table.

This is general advice. For specifics, edit your question to include query, table definitions and a query plan.

0
3

Isn't there tons of information out there about this? I learned SQL on MySQL and parlayed that into SQL Server, though, so maybe the open source documentation is just better.

The basic thing you need to know is what indexes are for. Then you'll know if you "should" use them or not.

Basically, ask yourself if you will be using a column anywhere other than the SELECT clause of the SQL statement. If yes, consider indexing it. If not, there's much less reason to. As far as I know, the columns listed in the SELECT are far less "expensive" than the ones in JOIN, WHERE, GROUP BY, ORDER BY, etc.

And if yes, the next question is harder to answer, because as mentioned in other answers, there are costs and benefits to indexing. I think it is untrue that indexing increases the table size by the size of the columns you index. The size increase, I believe, is proportional to the cardinality of the index. But in any case it does increase time for inserts. So, if your table is rarely modified and mainly just read from, the. By all means add every possible index "just in case."

But if the table is frequently written to, be more careful. Again most of my knowledge is from MySQL but I believe these apply here too.

  1. In MySQL, and probably in SQL Server as well, the first column in a compound index does not need its own separate index.
  2. The order of the columns in a compound index is important. It should match the orders used in your statements.
  3. If a query is rarely used, the cost of adding an index to speed it up might not be worth the small benefit.
  4. If a query is frequently used and the table is large, definitely consider indexing to speed it up.

MySQL and SQL Server both allow you to view query execution plans. You can use that to help determine which column to index (use Google to find more on that)

MySQL has a "slow query log" and I'm sure SQL Server has something similar to help you determine which queries are taking too long (Google it)

1

Here's a very common 2-column index with two indexes, each containing all 2 columns: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

It is a many-to-many mapping table. It has two ids for joining to the two table that are in a many-to-many relationship.

But this is a special case.

Here's another special case. Do you know what a "covering" index is? It is one that contains all the columns mentioned in a SELECT. But simply having a covering index is no better than simply having the table, itself. However,... If the start of the covering index contains column(s) that are useful for optimizing the WHERE, and the rest of the columns were tacked on to make it "covering", bingo -- you have an index that with two advantages -- helping with the WHERE, and not having to hit the data since everything needed is in the index. Caveat: It is 'unwise' to have more than 5 columns in an index.

2
  • Say we have a table with 30 columns, would including all of them on the INDEX "INCLUDE" side, be wise? (Basically the query is SELECT * FROM .... WHERE <index column x 4>). It seems including them will bring speed non the less. But why is it so bad to read the data from the table itself rather? (Almost like a table is very bad to do its job at all) Oct 14, 2020 at 8:59
  • @Wasted_Coder - While a 30-column index is possible (I think), it is ill-advised (in my opinion). In many situations it is optimal to read from the table, sometimes even eschewing an obviously good index. There are too many variations; please provide a specific. What, exactly, is in that WHERE clause? What datatypes are the columns? Etc, etc.
    – Rick James
    Oct 14, 2020 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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