Otdfbt

Multi tool use

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;

2

3

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

share|improve this question

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 | 

2

3

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

share|improve this question

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

share|improve this question

edited May 21 at 13:48

yagmoth555♦

12.7k31842

asked Apr 30 '10 at 17:49

Tim CokerTim Coker

20338

select max(col_a) from table where col_b = 'avalue'

select col_c from table where col_b = 'avalue' and col_a = 'anothervalue'

share|improve this question

edited May 21 at 13:48

yagmoth555♦

12.7k31842

asked Apr 30 '10 at 17:49

Tim CokerTim Coker

20338

share|improve this question

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

asked Apr 30 '10 at 17:49

Tim CokerTim Coker

20338

asked Apr 30 '10 at 17:49

Tim CokerTim Coker

20338

2 Answers
2

active

oldest

votes

2

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.

share|improve this answer

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 | 

0

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/

share|improve this answer

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
);



);

draft saved

draft discarded

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

Name

Email

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

Name

Email

Required, but never shown

2

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.

share|improve this answer

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 | 

2

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.

share|improve this answer

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.

share|improve this answer

answered Apr 30 '10 at 18:48

David MDavid M

577312

share|improve this answer

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

0

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/

share|improve this answer

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 | 

0

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/

share|improve this answer

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/

share|improve this answer

answered Apr 30 '10 at 18:00

hdannielhdanniel

3,9331824

share|improve this answer

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