When to add an index to a SQL table?
I have a table called Foo that has two columns A and B. Both of these columns are foreign keys to two other tables. These columns together make up the primary key, and it has a clustered index for this primary key by default.
My question is... does it benefit me at all to have another non-clustered index for B? Or is this unnecessary?
sql
sql-server database edited Nov 14 '18 at 15:54
jarlh
29.3k52138
asked Nov 14 '18 at 15:51
RonApple1996RonApple1996
827
add a comment |
I have a table called Foo that has two columns A and B. Both of these columns are foreign keys to two other tables. These columns together make up the primary key, and it has a clustered index for this primary key by default.
My question is... does it benefit me at all to have another non-clustered index for B? Or is this unnecessary?
sql
sql-server database edited Nov 14 '18 at 15:54
jarlh
29.3k52138
asked Nov 14 '18 at 15:51
RonApple1996RonApple1996
827
3
Which dbms are you using? (Product specific question.)
– jarlh
Nov 14 '18 at 15:52
I'm using Microsoft SQL Server
– RonApple1996
Nov 14 '18 at 15:53
1
It could, yes. It depends on what your query predicates are and how this table is being used. You need to provide more info. Also, have any of your queries given you a missing index hint?
– scsimon
Nov 14 '18 at 15:58
Will you ever or often query the table knowing only the B value?
– Joel Coehoorn
Nov 14 '18 at 16:06
@JoelCoehoorn it's very often the table is only getting queried by B
– RonApple1996
Nov 14 '18 at 16:07
add a comment |
I have a table called Foo that has two columns A and B. Both of these columns are foreign keys to two other tables. These columns together make up the primary key, and it has a clustered index for this primary key by default.
My question is... does it benefit me at all to have another non-clustered index for B? Or is this unnecessary?
sql
sql-server database edited Nov 14 '18 at 15:54
jarlh
29.3k52138
asked Nov 14 '18 at 15:51
RonApple1996RonApple1996
827
I have a table called Foo that has two columns A and B. Both of these columns are foreign keys to two other tables. These columns together make up the primary key, and it has a clustered index for this primary key by default.
My question is... does it benefit me at all to have another non-clustered index for B? Or is this unnecessary?
sql
sql-server database sql
sql-server database edited Nov 14 '18 at 15:54
jarlh
29.3k52138
asked Nov 14 '18 at 15:51
RonApple1996RonApple1996
827
edited Nov 14 '18 at 15:54
jarlh
29.3k52138
asked Nov 14 '18 at 15:51
RonApple1996RonApple1996
827
edited Nov 14 '18 at 15:54
jarlh
29.3k52138
edited Nov 14 '18 at 15:54
jarlh
29.3k52138
edited Nov 14 '18 at 15:54
jarlh
29.3k52138
29.3k52138
asked Nov 14 '18 at 15:51
RonApple1996RonApple1996
827
asked Nov 14 '18 at 15:51
RonApple1996RonApple1996
827
asked Nov 14 '18 at 15:51
RonApple1996RonApple1996
827
827
3
Which dbms are you using? (Product specific question.)
– jarlh
Nov 14 '18 at 15:52
I'm using Microsoft SQL Server
– RonApple1996
Nov 14 '18 at 15:53
1
It could, yes. It depends on what your query predicates are and how this table is being used. You need to provide more info. Also, have any of your queries given you a missing index hint?
– scsimon
Nov 14 '18 at 15:58
Will you ever or often query the table knowing only the B value?
– Joel Coehoorn
Nov 14 '18 at 16:06
@JoelCoehoorn it's very often the table is only getting queried by B
– RonApple1996
Nov 14 '18 at 16:07
add a comment |
3
Which dbms are you using? (Product specific question.)
– jarlh
Nov 14 '18 at 15:52
I'm using Microsoft SQL Server
– RonApple1996
Nov 14 '18 at 15:53
1
It could, yes. It depends on what your query predicates are and how this table is being used. You need to provide more info. Also, have any of your queries given you a missing index hint?
– scsimon
Nov 14 '18 at 15:58
Will you ever or often query the table knowing only the B value?
– Joel Coehoorn
Nov 14 '18 at 16:06
@JoelCoehoorn it's very often the table is only getting queried by B
– RonApple1996
Nov 14 '18 at 16:07
3
3
Which dbms are you using? (Product specific question.)
– jarlh
Nov 14 '18 at 15:52
Which dbms are you using? (Product specific question.)
– jarlh
Nov 14 '18 at 15:52
I'm using Microsoft SQL Server
– RonApple1996
Nov 14 '18 at 15:53
I'm using Microsoft SQL Server
– RonApple1996
Nov 14 '18 at 15:53
1
1
It could, yes. It depends on what your query predicates are and how this table is being used. You need to provide more info. Also, have any of your queries given you a missing index hint?
– scsimon
Nov 14 '18 at 15:58
It could, yes. It depends on what your query predicates are and how this table is being used. You need to provide more info. Also, have any of your queries given you a missing index hint?
– scsimon
Nov 14 '18 at 15:58
Will you ever or often query the table knowing only the B value?
– Joel Coehoorn
Nov 14 '18 at 16:06
Will you ever or often query the table knowing only the B value?
– Joel Coehoorn
Nov 14 '18 at 16:06
@JoelCoehoorn it's very often the table is only getting queried by B
– RonApple1996
Nov 14 '18 at 16:07
@JoelCoehoorn it's very often the table is only getting queried by B
– RonApple1996
Nov 14 '18 at 16:07
add a comment |
2 Answers
2
active
oldest
votes
First, some background, to make sure we're on the same page.
It sounds like you have also have tables Bar, with A as the key, and Baz, with B as the key, where the Foo table maintains a many-to-many intersection relationship between Bar and Baz, like this:
Bar(A) <=> Foo(A,B) <=> Baz(B)
If that's true, you need to ask yourself how you'll use the relationship. If you will start from Bar more often, and then need to discover related Baz values, then the primary (clustered) key should be (A, B). If you'll start from Baz more often, and then need to get the Bar values, then the primary key should be (B, A).
For this question, we are given the (A, B) situation, and asked whether an additional (B, A) key is a good idea. So we should assume you will more often start from a Bar record and need to know related Baz records: Bar -> Foo -> Baz, or at worst it's 50/50.
Now to answer the question.
The additional (B, A) index (or even just (B) INCLUDES A) may be helpful if you will also sometimes start from a Baz record and need to know related Bar records (Baz -> Foo -> Bar)... when you have queries going through Foo in both directions.
But it's also important to remember additional indexes have storage, memory, and maintenance costs. Whether the additional index will have a net-positive impact on your application depends on if you will use this kind of lookup often enough to overcome those costs. It's also worth mentioning the costs will be higher if the table changes often, because every change must now also update both indexes.
answered Nov 14 '18 at 16:14
Joel CoehoornJoel Coehoorn
309k95494727
add a comment |
Cannot say for sure without some detailed knowledge of your database workload. It will depend upon your usage.
If you query a lot from B to the shared table then its likely having an index on B will help. There are various tools you can use to see if you should optimise your indices based upon your workload including the Database Engine Tuning Advisor that comes with SQL Server.
Additionally if you have a referential integrity constriant on the B foreign key it generally helps to also have an index.
btw I am assuming your primary key is A followed by B and is the clustered index. Therefore the clustered index handles A and there is no need to have a separate index on A.
answered Nov 14 '18 at 16:07
PhilSPhilS
56025
Ok.. sorry I have another question with this answer. If it was "B followed by A", then I don't need another index for only B? How does the order of the primary key make a difference?
– RonApple1996
Nov 14 '18 at 16:09
I am assuming the Primary key is also the clustered index on the table. If so the rows will be saved in the order of the tuple (A,B) which means that SQL server knows the range where all rows with A are
– PhilS
Nov 14 '18 at 16:17
per PhilS' answer you can look at the execution plan of the query and if an index IS missing Sql server will suggest it to you Ron. But keep in mind what @Joel has said top side that indexes have cost. Someone has to rebuild and re-index them from time to time (be you or a DBA).
– junketsu
Nov 14 '18 at 20:22
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53304048%2fwhen-to-add-an-index-to-a-sql-table%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
First, some background, to make sure we're on the same page.
It sounds like you have also have tables Bar, with A as the key, and Baz, with B as the key, where the Foo table maintains a many-to-many intersection relationship between Bar and Baz, like this:
Bar(A) <=> Foo(A,B) <=> Baz(B)
If that's true, you need to ask yourself how you'll use the relationship. If you will start from Bar more often, and then need to discover related Baz values, then the primary (clustered) key should be (A, B). If you'll start from Baz more often, and then need to get the Bar values, then the primary key should be (B, A).
For this question, we are given the (A, B) situation, and asked whether an additional (B, A) key is a good idea. So we should assume you will more often start from a Bar record and need to know related Baz records: Bar -> Foo -> Baz, or at worst it's 50/50.
Now to answer the question.
The additional (B, A) index (or even just (B) INCLUDES A) may be helpful if you will also sometimes start from a Baz record and need to know related Bar records (Baz -> Foo -> Bar)... when you have queries going through Foo in both directions.
But it's also important to remember additional indexes have storage, memory, and maintenance costs. Whether the additional index will have a net-positive impact on your application depends on if you will use this kind of lookup often enough to overcome those costs. It's also worth mentioning the costs will be higher if the table changes often, because every change must now also update both indexes.
answered Nov 14 '18 at 16:14
Joel CoehoornJoel Coehoorn
309k95494727
add a comment |
First, some background, to make sure we're on the same page.
It sounds like you have also have tables Bar, with A as the key, and Baz, with B as the key, where the Foo table maintains a many-to-many intersection relationship between Bar and Baz, like this:
Bar(A) <=> Foo(A,B) <=> Baz(B)
If that's true, you need to ask yourself how you'll use the relationship. If you will start from Bar more often, and then need to discover related Baz values, then the primary (clustered) key should be (A, B). If you'll start from Baz more often, and then need to get the Bar values, then the primary key should be (B, A).
For this question, we are given the (A, B) situation, and asked whether an additional (B, A) key is a good idea. So we should assume you will more often start from a Bar record and need to know related Baz records: Bar -> Foo -> Baz, or at worst it's 50/50.
Now to answer the question.
The additional (B, A) index (or even just (B) INCLUDES A) may be helpful if you will also sometimes start from a Baz record and need to know related Bar records (Baz -> Foo -> Bar)... when you have queries going through Foo in both directions.
But it's also important to remember additional indexes have storage, memory, and maintenance costs. Whether the additional index will have a net-positive impact on your application depends on if you will use this kind of lookup often enough to overcome those costs. It's also worth mentioning the costs will be higher if the table changes often, because every change must now also update both indexes.
answered Nov 14 '18 at 16:14
Joel CoehoornJoel Coehoorn
309k95494727
add a comment |
First, some background, to make sure we're on the same page.
It sounds like you have also have tables Bar, with A as the key, and Baz, with B as the key, where the Foo table maintains a many-to-many intersection relationship between Bar and Baz, like this:
Bar(A) <=> Foo(A,B) <=> Baz(B)
If that's true, you need to ask yourself how you'll use the relationship. If you will start from Bar more often, and then need to discover related Baz values, then the primary (clustered) key should be (A, B). If you'll start from Baz more often, and then need to get the Bar values, then the primary key should be (B, A).
For this question, we are given the (A, B) situation, and asked whether an additional (B, A) key is a good idea. So we should assume you will more often start from a Bar record and need to know related Baz records: Bar -> Foo -> Baz, or at worst it's 50/50.
Now to answer the question.
The additional (B, A) index (or even just (B) INCLUDES A) may be helpful if you will also sometimes start from a Baz record and need to know related Bar records (Baz -> Foo -> Bar)... when you have queries going through Foo in both directions.
But it's also important to remember additional indexes have storage, memory, and maintenance costs. Whether the additional index will have a net-positive impact on your application depends on if you will use this kind of lookup often enough to overcome those costs. It's also worth mentioning the costs will be higher if the table changes often, because every change must now also update both indexes.
answered Nov 14 '18 at 16:14
Joel CoehoornJoel Coehoorn
309k95494727
First, some background, to make sure we're on the same page.
It sounds like you have also have tables Bar, with A as the key, and Baz, with B as the key, where the Foo table maintains a many-to-many intersection relationship between Bar and Baz, like this:
Bar(A) <=> Foo(A,B) <=> Baz(B)
If that's true, you need to ask yourself how you'll use the relationship. If you will start from Bar more often, and then need to discover related Baz values, then the primary (clustered) key should be (A, B). If you'll start from Baz more often, and then need to get the Bar values, then the primary key should be (B, A).
For this question, we are given the (A, B) situation, and asked whether an additional (B, A) key is a good idea. So we should assume you will more often start from a Bar record and need to know related Baz records: Bar -> Foo -> Baz, or at worst it's 50/50.
Now to answer the question.
The additional (B, A) index (or even just (B) INCLUDES A) may be helpful if you will also sometimes start from a Baz record and need to know related Bar records (Baz -> Foo -> Bar)... when you have queries going through Foo in both directions.
But it's also important to remember additional indexes have storage, memory, and maintenance costs. Whether the additional index will have a net-positive impact on your application depends on if you will use this kind of lookup often enough to overcome those costs. It's also worth mentioning the costs will be higher if the table changes often, because every change must now also update both indexes.
answered Nov 14 '18 at 16:14
Joel CoehoornJoel Coehoorn
309k95494727
edited Nov 15 '18 at 17:12
answered Nov 14 '18 at 16:14
Joel CoehoornJoel Coehoorn
309k95494727
answered Nov 14 '18 at 16:14
Joel CoehoornJoel Coehoorn
309k95494727
answered Nov 14 '18 at 16:14
Joel CoehoornJoel Coehoorn
309k95494727
309k95494727
add a comment |
add a comment |
Cannot say for sure without some detailed knowledge of your database workload. It will depend upon your usage.
If you query a lot from B to the shared table then its likely having an index on B will help. There are various tools you can use to see if you should optimise your indices based upon your workload including the Database Engine Tuning Advisor that comes with SQL Server.
Additionally if you have a referential integrity constriant on the B foreign key it generally helps to also have an index.
btw I am assuming your primary key is A followed by B and is the clustered index. Therefore the clustered index handles A and there is no need to have a separate index on A.
answered Nov 14 '18 at 16:07
PhilSPhilS
56025
Ok.. sorry I have another question with this answer. If it was "B followed by A", then I don't need another index for only B? How does the order of the primary key make a difference?
– RonApple1996
Nov 14 '18 at 16:09
I am assuming the Primary key is also the clustered index on the table. If so the rows will be saved in the order of the tuple (A,B) which means that SQL server knows the range where all rows with A are
– PhilS
Nov 14 '18 at 16:17
per PhilS' answer you can look at the execution plan of the query and if an index IS missing Sql server will suggest it to you Ron. But keep in mind what @Joel has said top side that indexes have cost. Someone has to rebuild and re-index them from time to time (be you or a DBA).
– junketsu
Nov 14 '18 at 20:22
add a comment |
Cannot say for sure without some detailed knowledge of your database workload. It will depend upon your usage.
If you query a lot from B to the shared table then its likely having an index on B will help. There are various tools you can use to see if you should optimise your indices based upon your workload including the Database Engine Tuning Advisor that comes with SQL Server.
Additionally if you have a referential integrity constriant on the B foreign key it generally helps to also have an index.
btw I am assuming your primary key is A followed by B and is the clustered index. Therefore the clustered index handles A and there is no need to have a separate index on A.
answered Nov 14 '18 at 16:07
PhilSPhilS
56025
Ok.. sorry I have another question with this answer. If it was "B followed by A", then I don't need another index for only B? How does the order of the primary key make a difference?
– RonApple1996
Nov 14 '18 at 16:09
I am assuming the Primary key is also the clustered index on the table. If so the rows will be saved in the order of the tuple (A,B) which means that SQL server knows the range where all rows with A are
– PhilS
Nov 14 '18 at 16:17
per PhilS' answer you can look at the execution plan of the query and if an index IS missing Sql server will suggest it to you Ron. But keep in mind what @Joel has said top side that indexes have cost. Someone has to rebuild and re-index them from time to time (be you or a DBA).
– junketsu
Nov 14 '18 at 20:22
add a comment |
Cannot say for sure without some detailed knowledge of your database workload. It will depend upon your usage.
If you query a lot from B to the shared table then its likely having an index on B will help. There are various tools you can use to see if you should optimise your indices based upon your workload including the Database Engine Tuning Advisor that comes with SQL Server.
Additionally if you have a referential integrity constriant on the B foreign key it generally helps to also have an index.
btw I am assuming your primary key is A followed by B and is the clustered index. Therefore the clustered index handles A and there is no need to have a separate index on A.
answered Nov 14 '18 at 16:07
PhilSPhilS
56025
Cannot say for sure without some detailed knowledge of your database workload. It will depend upon your usage.
If you query a lot from B to the shared table then its likely having an index on B will help. There are various tools you can use to see if you should optimise your indices based upon your workload including the Database Engine Tuning Advisor that comes with SQL Server.
Additionally if you have a referential integrity constriant on the B foreign key it generally helps to also have an index.
btw I am assuming your primary key is A followed by B and is the clustered index. Therefore the clustered index handles A and there is no need to have a separate index on A.
answered Nov 14 '18 at 16:07
PhilSPhilS
56025
edited Nov 14 '18 at 16:20
answered Nov 14 '18 at 16:07
PhilSPhilS
56025
answered Nov 14 '18 at 16:07
PhilSPhilS
56025
answered Nov 14 '18 at 16:07
PhilSPhilS
56025
56025
Ok.. sorry I have another question with this answer. If it was "B followed by A", then I don't need another index for only B? How does the order of the primary key make a difference?
– RonApple1996
Nov 14 '18 at 16:09
I am assuming the Primary key is also the clustered index on the table. If so the rows will be saved in the order of the tuple (A,B) which means that SQL server knows the range where all rows with A are
– PhilS
Nov 14 '18 at 16:17
per PhilS' answer you can look at the execution plan of the query and if an index IS missing Sql server will suggest it to you Ron. But keep in mind what @Joel has said top side that indexes have cost. Someone has to rebuild and re-index them from time to time (be you or a DBA).
– junketsu
Nov 14 '18 at 20:22
add a comment |
Ok.. sorry I have another question with this answer. If it was "B followed by A", then I don't need another index for only B? How does the order of the primary key make a difference?
– RonApple1996
Nov 14 '18 at 16:09
I am assuming the Primary key is also the clustered index on the table. If so the rows will be saved in the order of the tuple (A,B) which means that SQL server knows the range where all rows with A are
– PhilS
Nov 14 '18 at 16:17
per PhilS' answer you can look at the execution plan of the query and if an index IS missing Sql server will suggest it to you Ron. But keep in mind what @Joel has said top side that indexes have cost. Someone has to rebuild and re-index them from time to time (be you or a DBA).
– junketsu
Nov 14 '18 at 20:22
Ok.. sorry I have another question with this answer. If it was "B followed by A", then I don't need another index for only B? How does the order of the primary key make a difference?
– RonApple1996
Nov 14 '18 at 16:09
Ok.. sorry I have another question with this answer. If it was "B followed by A", then I don't need another index for only B? How does the order of the primary key make a difference?
– RonApple1996
Nov 14 '18 at 16:09
I am assuming the Primary key is also the clustered index on the table. If so the rows will be saved in the order of the tuple (A,B) which means that SQL server knows the range where all rows with A are
– PhilS
Nov 14 '18 at 16:17
I am assuming the Primary key is also the clustered index on the table. If so the rows will be saved in the order of the tuple (A,B) which means that SQL server knows the range where all rows with A are
– PhilS
Nov 14 '18 at 16:17
per PhilS' answer you can look at the execution plan of the query and if an index IS missing Sql server will suggest it to you Ron. But keep in mind what @Joel has said top side that indexes have cost. Someone has to rebuild and re-index them from time to time (be you or a DBA).
– junketsu
Nov 14 '18 at 20:22
per PhilS' answer you can look at the execution plan of the query and if an index IS missing Sql server will suggest it to you Ron. But keep in mind what @Joel has said top side that indexes have cost. Someone has to rebuild and re-index them from time to time (be you or a DBA).
– junketsu
Nov 14 '18 at 20:22
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53304048%2fwhen-to-add-an-index-to-a-sql-table%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
Which dbms are you using? (Product specific question.)
– jarlh
Nov 14 '18 at 15:52
I'm using Microsoft SQL Server
– RonApple1996
Nov 14 '18 at 15:53
1
It could, yes. It depends on what your query predicates are and how this table is being used. You need to provide more info. Also, have any of your queries given you a missing index hint?
– scsimon
Nov 14 '18 at 15:58
Will you ever or often query the table knowing only the B value?
– Joel Coehoorn
Nov 14 '18 at 16:06
@JoelCoehoorn it's very often the table is only getting queried by B
– RonApple1996
Nov 14 '18 at 16:07