Multiple columns in a single index versus multiple indexesStoring changes to multiple databases in a single centralized databaseBad performance for a large join query in PostgreSQL 8.4.4, despite enough memory to cache entirelyWhat would be better solution a single database or multiple databases, one for each customerDoes the order of creating a columns for a table impact performance?Multiple SQL Server unique indexes with different includesHow do multiple servers work in terms of databases?Big SQL database platform that performs wellSQL Server seems to be taking 40GB of RAM but keeps reading disk… Why?Large database table queries return empty setHow can I further improve Oracle 12.1 performance on RDS m4.large instance?
Can a black dragonborn's acid breath weapon destroy objects?
Compiling c files on ubuntu and using the executable on Windows
Confusion about off peak timings of London trains
Frame failure sudden death?
How does an ordinary object become radioactive?
Movie about a boy who was born old and grew young
Was the output of the C64 SID chip 8 bit sound?
Is the term 'open source' a trademark?
What is wrong with this proof that symmetric matrices commute?
Can a user sell my software (MIT license) without modification?
Words that signal future content
How can I most clearly write a homebrew item that affects the ground below its radius after the initial explosion it creates?
Using a found spellbook as a Sorcerer-Wizard multiclass
Why would future John risk sending back a T-800 to save his younger self?
Do any instruments not produce overtones?
Watts vs. Volt Amps
What is the actual quality of machine translations?
Was the Tamarian language in "Darmok" inspired by Jack Vance's "The Asutra"?
How water is heavier than petrol eventhough its molecular weight less than petrol?
Where does "0 packages can be updated." come from?
Does an ice chest packed full of frozen food need ice?
Can the poison from Kingsmen be concocted?
Passing multiple files through stdin (over ssh)
Was Jesus good at singing?
Multiple columns in a single index versus multiple indexes
Storing changes to multiple databases in a single centralized databaseBad performance for a large join query in PostgreSQL 8.4.4, despite enough memory to cache entirelyWhat would be better solution a single database or multiple databases, one for each customerDoes the order of creating a columns for a table impact performance?Multiple SQL Server unique indexes with different includesHow do multiple servers work in terms of databases?Big SQL database platform that performs wellSQL Server seems to be taking 40GB of RAM but keeps reading disk… Why?Large database table queries return empty setHow can I further improve Oracle 12.1 performance on RDS m4.large instance?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
The short version of my question is what's the difference between three indexes each indexing a single column and one index indexing three columns. Background follows.
I'm primarily a programmer but have to do DBA work because we don't have a DBA. I'm evaluating our indexes versus the queries run against a particular table. The table as 3 columns that I'm often filtering against or getting the max value of. Most of the time the queries look like
select max(col_a) from table where col_b = 'avalue'
or
select col_c from table where col_b = 'avalue' and col_a = 'anothervalue'
All columns are independently indexed. My question is would I see any difference if I had an index that indexed col_b and col_a together since they can appear in a where clause together?
database database-performance
edited May 21 at 13:48
yagmoth555♦
12.7k31842
asked Apr 30 '10 at 17:49
Tim CokerTim Coker
20338
add a comment |
The short version of my question is what's the difference between three indexes each indexing a single column and one index indexing three columns. Background follows.
I'm primarily a programmer but have to do DBA work because we don't have a DBA. I'm evaluating our indexes versus the queries run against a particular table. The table as 3 columns that I'm often filtering against or getting the max value of. Most of the time the queries look like
select max(col_a) from table where col_b = 'avalue'
or
select col_c from table where col_b = 'avalue' and col_a = 'anothervalue'
All columns are independently indexed. My question is would I see any difference if I had an index that indexed col_b and col_a together since they can appear in a where clause together?
database database-performance
edited May 21 at 13:48
yagmoth555♦
12.7k31842
asked Apr 30 '10 at 17:49
Tim CokerTim Coker
20338
1
I would say stackoverflow for this one, but not going to vote for that because I'm not really that sure...
– Kyle Brandt
Apr 30 '10 at 17:53
I just found a kinda related question on stack overflow, actually. Doesn't directly answer the question, though. stackoverflow.com/questions/2713438/…
– Tim Coker
Apr 30 '10 at 17:57
add a comment |
The short version of my question is what's the difference between three indexes each indexing a single column and one index indexing three columns. Background follows.
I'm primarily a programmer but have to do DBA work because we don't have a DBA. I'm evaluating our indexes versus the queries run against a particular table. The table as 3 columns that I'm often filtering against or getting the max value of. Most of the time the queries look like
select max(col_a) from table where col_b = 'avalue'
or
select col_c from table where col_b = 'avalue' and col_a = 'anothervalue'
All columns are independently indexed. My question is would I see any difference if I had an index that indexed col_b and col_a together since they can appear in a where clause together?
database database-performance
edited May 21 at 13:48
yagmoth555♦
12.7k31842
asked Apr 30 '10 at 17:49
Tim CokerTim Coker
20338
The short version of my question is what's the difference between three indexes each indexing a single column and one index indexing three columns. Background follows.
I'm primarily a programmer but have to do DBA work because we don't have a DBA. I'm evaluating our indexes versus the queries run against a particular table. The table as 3 columns that I'm often filtering against or getting the max value of. Most of the time the queries look like
select max(col_a) from table where col_b = 'avalue'
or
select col_c from table where col_b = 'avalue' and col_a = 'anothervalue'
All columns are independently indexed. My question is would I see any difference if I had an index that indexed col_b and col_a together since they can appear in a where clause together?
database database-performance
database database-performance
edited May 21 at 13:48
yagmoth555♦
12.7k31842
asked Apr 30 '10 at 17:49
Tim CokerTim Coker
20338
edited May 21 at 13:48
yagmoth555♦
12.7k31842
asked Apr 30 '10 at 17:49
Tim CokerTim Coker
20338
edited May 21 at 13:48
yagmoth555♦
12.7k31842
edited May 21 at 13:48
yagmoth555♦
12.7k31842
edited May 21 at 13:48
yagmoth555♦
12.7k31842
12.7k31842
asked Apr 30 '10 at 17:49
Tim CokerTim Coker
20338
asked Apr 30 '10 at 17:49
Tim CokerTim Coker
20338
asked Apr 30 '10 at 17:49
Tim CokerTim Coker
20338
20338
1
I would say stackoverflow for this one, but not going to vote for that because I'm not really that sure...
– Kyle Brandt
Apr 30 '10 at 17:53
I just found a kinda related question on stack overflow, actually. Doesn't directly answer the question, though. stackoverflow.com/questions/2713438/…
– Tim Coker
Apr 30 '10 at 17:57
add a comment |
1
I would say stackoverflow for this one, but not going to vote for that because I'm not really that sure...
– Kyle Brandt
Apr 30 '10 at 17:53
I just found a kinda related question on stack overflow, actually. Doesn't directly answer the question, though. stackoverflow.com/questions/2713438/…
– Tim Coker
Apr 30 '10 at 17:57
1
1
I would say stackoverflow for this one, but not going to vote for that because I'm not really that sure...
– Kyle Brandt
Apr 30 '10 at 17:53
I would say stackoverflow for this one, but not going to vote for that because I'm not really that sure...
– Kyle Brandt
Apr 30 '10 at 17:53
I just found a kinda related question on stack overflow, actually. Doesn't directly answer the question, though. stackoverflow.com/questions/2713438/…
– Tim Coker
Apr 30 '10 at 17:57
I just found a kinda related question on stack overflow, actually. Doesn't directly answer the question, though. stackoverflow.com/questions/2713438/…
– Tim Coker
Apr 30 '10 at 17:57
add a comment |
2 Answers
2
active
oldest
votes
For simple examples for questions like these, it's easy to tell which is better. For real-world circumstances, your mileage may vary significantly based on other parts of the query.
In MySQL, and perhaps other databases, you could create an index on (col_b, col_a) and use it for both of your queries - any left-most portion of the multi-column index is itself an index. An index on (col1, col2, col3) is also an index on (col1) and (col1, col2).
To answer your specific question, I think you would derive significant benefit from indexing col_b and col_a together. If you stick with just the independent indexes for each column, one of two things happens - either the database uses one of the indexes to consider a subset of rows that match one of the values and then scans them for the second value, or it does some fancy on-the-fly index merging to emulate an index on both columns. MySQL 4 would do the former, and MySQL can do the latter. In either of those cases, you might as well create the combined index, because you expect to use it.
answered Apr 30 '10 at 18:48
David MDavid M
577312
Thanks. I'm curious about this issue in the general sense, so the above info is good to know. Do you have any idea how much of this applies to Oracle indexing? Specifically the bit about a multi-column index acting the same as a single column index for the left most column in said index.
– Tim Coker
Apr 30 '10 at 19:21
That is pretty much the case everywhere.
– Peter Eisentraut
Apr 30 '10 at 23:18
add a comment |
In MySQL, for most cases one single index is faster. You can see some benchmarks here:
http://www.mysqlperformanceblog.com/2008/08/22/multiple-column-index-vs-multiple-indexes/
answered Apr 30 '10 at 18:00
hdannielhdanniel
3,9331824
Thanks, that's good to know. I have to work with MySQL at times. I happen to be working with Oracle, which the SO question linked above is talking about. Reading that question makes me glad I don't work with Oracle much.
– Tim Coker
Apr 30 '10 at 18:27
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "2"
;
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%2fserverfault.com%2fquestions%2f137483%2fmultiple-columns-in-a-single-index-versus-multiple-indexes%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
For simple examples for questions like these, it's easy to tell which is better. For real-world circumstances, your mileage may vary significantly based on other parts of the query.
In MySQL, and perhaps other databases, you could create an index on (col_b, col_a) and use it for both of your queries - any left-most portion of the multi-column index is itself an index. An index on (col1, col2, col3) is also an index on (col1) and (col1, col2).
To answer your specific question, I think you would derive significant benefit from indexing col_b and col_a together. If you stick with just the independent indexes for each column, one of two things happens - either the database uses one of the indexes to consider a subset of rows that match one of the values and then scans them for the second value, or it does some fancy on-the-fly index merging to emulate an index on both columns. MySQL 4 would do the former, and MySQL can do the latter. In either of those cases, you might as well create the combined index, because you expect to use it.
answered Apr 30 '10 at 18:48
David MDavid M
577312
Thanks. I'm curious about this issue in the general sense, so the above info is good to know. Do you have any idea how much of this applies to Oracle indexing? Specifically the bit about a multi-column index acting the same as a single column index for the left most column in said index.
– Tim Coker
Apr 30 '10 at 19:21
That is pretty much the case everywhere.
– Peter Eisentraut
Apr 30 '10 at 23:18
add a comment |
For simple examples for questions like these, it's easy to tell which is better. For real-world circumstances, your mileage may vary significantly based on other parts of the query.
In MySQL, and perhaps other databases, you could create an index on (col_b, col_a) and use it for both of your queries - any left-most portion of the multi-column index is itself an index. An index on (col1, col2, col3) is also an index on (col1) and (col1, col2).
To answer your specific question, I think you would derive significant benefit from indexing col_b and col_a together. If you stick with just the independent indexes for each column, one of two things happens - either the database uses one of the indexes to consider a subset of rows that match one of the values and then scans them for the second value, or it does some fancy on-the-fly index merging to emulate an index on both columns. MySQL 4 would do the former, and MySQL can do the latter. In either of those cases, you might as well create the combined index, because you expect to use it.
answered Apr 30 '10 at 18:48
David MDavid M
577312
Thanks. I'm curious about this issue in the general sense, so the above info is good to know. Do you have any idea how much of this applies to Oracle indexing? Specifically the bit about a multi-column index acting the same as a single column index for the left most column in said index.
– Tim Coker
Apr 30 '10 at 19:21
That is pretty much the case everywhere.
– Peter Eisentraut
Apr 30 '10 at 23:18
add a comment |
For simple examples for questions like these, it's easy to tell which is better. For real-world circumstances, your mileage may vary significantly based on other parts of the query.
In MySQL, and perhaps other databases, you could create an index on (col_b, col_a) and use it for both of your queries - any left-most portion of the multi-column index is itself an index. An index on (col1, col2, col3) is also an index on (col1) and (col1, col2).
To answer your specific question, I think you would derive significant benefit from indexing col_b and col_a together. If you stick with just the independent indexes for each column, one of two things happens - either the database uses one of the indexes to consider a subset of rows that match one of the values and then scans them for the second value, or it does some fancy on-the-fly index merging to emulate an index on both columns. MySQL 4 would do the former, and MySQL can do the latter. In either of those cases, you might as well create the combined index, because you expect to use it.
answered Apr 30 '10 at 18:48
David MDavid M
577312
For simple examples for questions like these, it's easy to tell which is better. For real-world circumstances, your mileage may vary significantly based on other parts of the query.
In MySQL, and perhaps other databases, you could create an index on (col_b, col_a) and use it for both of your queries - any left-most portion of the multi-column index is itself an index. An index on (col1, col2, col3) is also an index on (col1) and (col1, col2).
To answer your specific question, I think you would derive significant benefit from indexing col_b and col_a together. If you stick with just the independent indexes for each column, one of two things happens - either the database uses one of the indexes to consider a subset of rows that match one of the values and then scans them for the second value, or it does some fancy on-the-fly index merging to emulate an index on both columns. MySQL 4 would do the former, and MySQL can do the latter. In either of those cases, you might as well create the combined index, because you expect to use it.
answered Apr 30 '10 at 18:48
David MDavid M
577312
answered Apr 30 '10 at 18:48
David MDavid M
577312
answered Apr 30 '10 at 18:48
David MDavid M
577312
answered Apr 30 '10 at 18:48
David MDavid M
577312
577312
Thanks. I'm curious about this issue in the general sense, so the above info is good to know. Do you have any idea how much of this applies to Oracle indexing? Specifically the bit about a multi-column index acting the same as a single column index for the left most column in said index.
– Tim Coker
Apr 30 '10 at 19:21
That is pretty much the case everywhere.
– Peter Eisentraut
Apr 30 '10 at 23:18
add a comment |
Thanks. I'm curious about this issue in the general sense, so the above info is good to know. Do you have any idea how much of this applies to Oracle indexing? Specifically the bit about a multi-column index acting the same as a single column index for the left most column in said index.
– Tim Coker
Apr 30 '10 at 19:21
That is pretty much the case everywhere.
– Peter Eisentraut
Apr 30 '10 at 23:18
Thanks. I'm curious about this issue in the general sense, so the above info is good to know. Do you have any idea how much of this applies to Oracle indexing? Specifically the bit about a multi-column index acting the same as a single column index for the left most column in said index.
– Tim Coker
Apr 30 '10 at 19:21
Thanks. I'm curious about this issue in the general sense, so the above info is good to know. Do you have any idea how much of this applies to Oracle indexing? Specifically the bit about a multi-column index acting the same as a single column index for the left most column in said index.
– Tim Coker
Apr 30 '10 at 19:21
That is pretty much the case everywhere.
– Peter Eisentraut
Apr 30 '10 at 23:18
That is pretty much the case everywhere.
– Peter Eisentraut
Apr 30 '10 at 23:18
add a comment |
In MySQL, for most cases one single index is faster. You can see some benchmarks here:
http://www.mysqlperformanceblog.com/2008/08/22/multiple-column-index-vs-multiple-indexes/
answered Apr 30 '10 at 18:00
hdannielhdanniel
3,9331824
Thanks, that's good to know. I have to work with MySQL at times. I happen to be working with Oracle, which the SO question linked above is talking about. Reading that question makes me glad I don't work with Oracle much.
– Tim Coker
Apr 30 '10 at 18:27
add a comment |
In MySQL, for most cases one single index is faster. You can see some benchmarks here:
http://www.mysqlperformanceblog.com/2008/08/22/multiple-column-index-vs-multiple-indexes/
answered Apr 30 '10 at 18:00
hdannielhdanniel
3,9331824
Thanks, that's good to know. I have to work with MySQL at times. I happen to be working with Oracle, which the SO question linked above is talking about. Reading that question makes me glad I don't work with Oracle much.
– Tim Coker
Apr 30 '10 at 18:27
add a comment |
In MySQL, for most cases one single index is faster. You can see some benchmarks here:
http://www.mysqlperformanceblog.com/2008/08/22/multiple-column-index-vs-multiple-indexes/
answered Apr 30 '10 at 18:00
hdannielhdanniel
3,9331824
In MySQL, for most cases one single index is faster. You can see some benchmarks here:
http://www.mysqlperformanceblog.com/2008/08/22/multiple-column-index-vs-multiple-indexes/
answered Apr 30 '10 at 18:00
hdannielhdanniel
3,9331824
answered Apr 30 '10 at 18:00
hdannielhdanniel
3,9331824
answered Apr 30 '10 at 18:00
hdannielhdanniel
3,9331824
answered Apr 30 '10 at 18:00
hdannielhdanniel
3,9331824
3,9331824
Thanks, that's good to know. I have to work with MySQL at times. I happen to be working with Oracle, which the SO question linked above is talking about. Reading that question makes me glad I don't work with Oracle much.
– Tim Coker
Apr 30 '10 at 18:27
add a comment |
Thanks, that's good to know. I have to work with MySQL at times. I happen to be working with Oracle, which the SO question linked above is talking about. Reading that question makes me glad I don't work with Oracle much.
– Tim Coker
Apr 30 '10 at 18:27
Thanks, that's good to know. I have to work with MySQL at times. I happen to be working with Oracle, which the SO question linked above is talking about. Reading that question makes me glad I don't work with Oracle much.
– Tim Coker
Apr 30 '10 at 18:27
Thanks, that's good to know. I have to work with MySQL at times. I happen to be working with Oracle, which the SO question linked above is talking about. Reading that question makes me glad I don't work with Oracle much.
– Tim Coker
Apr 30 '10 at 18:27
add a comment |
Thanks for contributing an answer to Server Fault!
- 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%2fserverfault.com%2fquestions%2f137483%2fmultiple-columns-in-a-single-index-versus-multiple-indexes%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
1
I would say stackoverflow for this one, but not going to vote for that because I'm not really that sure...
– Kyle Brandt
Apr 30 '10 at 17:53
I just found a kinda related question on stack overflow, actually. Doesn't directly answer the question, though. stackoverflow.com/questions/2713438/…
– Tim Coker
Apr 30 '10 at 17:57