Styjun

How dangerous are set-size assumptions?

How do I respond to requests for a "guarantee" not to leave after a few months?

Find the probability that the 8th woman to appear is in 17th position.

Does Marvel have an equivalent of the Green Lantern?

Would it be a copyright violation if I made a character’s full name refer to a song?

How was Hillel permitted to go to the skylight to hear the shiur

Intuition for capacitors in series

Why did pressing the joystick button spit out keypresses?

Find the C-factor of a vote

Can ADFS connect to other SSO services?

Iterate MapThread with matrices

How would modern naval warfare have to have developed differently for battleships to still be relevant in the 21st century?

Fedora boot screen shows both Fedora logo and Lenovo logo. Why and How?

Is adding a new player (or players) a DM decision, or a group decision?

How do I turn off a repeating trade?

Does squid ink pasta bleed?

Do I have any obligations to my PhD supervisor's requests after I have graduated?

Why do some professors with PhDs leave their professorships to teach high school?

What is the mechanical difference between the Spectator's Create Food and Water action and the Banshee's Undead Nature Trait?

Is this one of the engines from the 9/11 aircraft?

Suggested order for Amazon Prime Doctor Who series

What is the legal status of travelling with methadone in your carry-on?

Can Ogre clerics use Purify Food and Drink on humanoid characters?

Links to webpages in books

Is this MySQL transposition group by query optimizable?

How to output MySQL query results in CSV format?Should I use the datetime or timestamp data type in MySQL?How to get a list of user accounts using the command line in MySQL?Correlated mysql subqueriesmySql: count number of rows that have the same data in a columnHow to update table data using group by in mysqlHow to import an SQL file using the command line in MySQL?Mysql - select multiple columns with same value and other colums equal to zeroDynamic sql query hence no. of columns varying than how to dynamically produce output in foreach segmentHow to display ALL the Non-Null and ALL the Non-Empty records without mentioning ALL the column-name in the where clause using a MySql query?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

my_table holds 290M rows and I wish to optimize the following query

select
 col1,
 col2,
 group_concat(distinct case when col3=1 then col4 end) c1,
 group_concat(distinct case when col3=2 then col4 end) c2,
 ...
 group_concat(distinct case when col3=70 then col4 end) c70
from my_table
group by col1,col2
order by null

I already tried running smaller queries like this one but the whole thing is worse

select
 col1,
 col2,
 group_concat(distinct case when col3=1 then col4 end) c1
from my_table
group by col1,col2
order by null

Is there a way to do it ?

mysql query-optimization pivot-table transpose query-performance

share|improve this question

edited Apr 17 at 3:18

Rick James

74.9k55 gold badges6868 silver badges110110 bronze badges

asked Mar 25 at 9:39

guigozguigoz

43422 silver badges1111 bronze badges

add a comment | 

0

my_table holds 290M rows and I wish to optimize the following query

select
 col1,
 col2,
 group_concat(distinct case when col3=1 then col4 end) c1,
 group_concat(distinct case when col3=2 then col4 end) c2,
 ...
 group_concat(distinct case when col3=70 then col4 end) c70
from my_table
group by col1,col2
order by null

I already tried running smaller queries like this one but the whole thing is worse

select
 col1,
 col2,
 group_concat(distinct case when col3=1 then col4 end) c1
from my_table
group by col1,col2
order by null

Is there a way to do it ?

mysql query-optimization pivot-table transpose query-performance

share|improve this question

edited Apr 17 at 3:18

Rick James

74.9k55 gold badges6868 silver badges110110 bronze badges

asked Mar 25 at 9:39

guigozguigoz

43422 silver badges1111 bronze badges

add a comment | 

my_table holds 290M rows and I wish to optimize the following query

select
 col1,
 col2,
 group_concat(distinct case when col3=1 then col4 end) c1,
 group_concat(distinct case when col3=2 then col4 end) c2,
 ...
 group_concat(distinct case when col3=70 then col4 end) c70
from my_table
group by col1,col2
order by null

I already tried running smaller queries like this one but the whole thing is worse

select
 col1,
 col2,
 group_concat(distinct case when col3=1 then col4 end) c1
from my_table
group by col1,col2
order by null

Is there a way to do it ?

mysql query-optimization pivot-table transpose query-performance

share|improve this question

edited Apr 17 at 3:18

Rick James

74.9k55 gold badges6868 silver badges110110 bronze badges

asked Mar 25 at 9:39

guigozguigoz

43422 silver badges1111 bronze badges

select
 col1,
 col2,
 group_concat(distinct case when col3=1 then col4 end) c1,
 group_concat(distinct case when col3=2 then col4 end) c2,
 ...
 group_concat(distinct case when col3=70 then col4 end) c70
from my_table
group by col1,col2
order by null

select
 col1,
 col2,
 group_concat(distinct case when col3=1 then col4 end) c1
from my_table
group by col1,col2
order by null

share|improve this question

edited Apr 17 at 3:18

Rick James

74.9k55 gold badges6868 silver badges110110 bronze badges

asked Mar 25 at 9:39

guigozguigoz

43422 silver badges1111 bronze badges

share|improve this question

edited Apr 17 at 3:18

Rick James

74.9k55 gold badges6868 silver badges110110 bronze badges

asked Mar 25 at 9:39

guigozguigoz

43422 silver badges1111 bronze badges

edited Apr 17 at 3:18

Rick James

74.9k55 gold badges6868 silver badges110110 bronze badges

edited Apr 17 at 3:18

Rick James

74.9k55 gold badges6868 silver badges110110 bronze badges

asked Mar 25 at 9:39

guigozguigoz

43422 silver badges1111 bronze badges

asked Mar 25 at 9:39

guigozguigoz

43422 silver badges1111 bronze badges

2 Answers
2

active

oldest

votes

0

This is a tough one, because you are querying against just a single table. I can suggest the following index:

CREATE INDEX idx ON my_table (col1, col2, col3, col4);

MySQL might choose to use this index, on the grounds that for every (col1, col2) group it can do an index scan to find each value of col3, and then concatenate together the distinct values of col4.

share|improve this answer

answered Mar 25 at 9:44

Tim BiegeleisenTim Biegeleisen

254k1313 gold badges106106 silver badges166166 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Will a explain show an index scan ? Can a do it on a sample (the last index I created took 5 hours)?
  
  – guigoz
  Mar 25 at 15:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, you may try using a smaller sample of your data.
  
  – Tim Biegeleisen
  Mar 25 at 23:32
  

  
  
  
  

add a comment | 

0

(Please use real column names; often there are useful clues there.)

Maybe this will be faster...

First, let's see how fast it is to do all the GROUP_CONCATs at once:

SELECT col3,
 GROUP_CONCAT(DISTINCT col4) AS list
 FROM my_table
 GROUP BY col3;

That will take a full table scan (290M rows), but it can be sped up with

INDEX(col3, col4) -- in this order

which is 'covering'.

However, since you have col1 and col2 muddying the works, let's change to

SELECT col1, col2, col3,
 GROUP_CONCAT(DISTINCT col4) AS list
 FROM my_table
 GROUP BY col1, col3, col3;

and

INDEX(col1, col2, col3, col4) -- in this order

At that point, you have all the data but need to "pivot" it. (See the [pivot] tag.)

share|improve this answer

answered Apr 17 at 3:17

Rick JamesRick James

74.9k55 gold badges6868 silver badges110110 bronze badges

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



);

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%2fstackoverflow.com%2fquestions%2f55334896%2fis-this-mysql-transposition-group-by-query-optimizable%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

This is a tough one, because you are querying against just a single table. I can suggest the following index:

CREATE INDEX idx ON my_table (col1, col2, col3, col4);

MySQL might choose to use this index, on the grounds that for every (col1, col2) group it can do an index scan to find each value of col3, and then concatenate together the distinct values of col4.

share|improve this answer

answered Mar 25 at 9:44

Tim BiegeleisenTim Biegeleisen

254k1313 gold badges106106 silver badges166166 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Will a explain show an index scan ? Can a do it on a sample (the last index I created took 5 hours)?
  
  – guigoz
  Mar 25 at 15:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, you may try using a smaller sample of your data.
  
  – Tim Biegeleisen
  Mar 25 at 23:32
  

  
  
  
  

add a comment | 

0

This is a tough one, because you are querying against just a single table. I can suggest the following index:

CREATE INDEX idx ON my_table (col1, col2, col3, col4);

MySQL might choose to use this index, on the grounds that for every (col1, col2) group it can do an index scan to find each value of col3, and then concatenate together the distinct values of col4.

share|improve this answer

answered Mar 25 at 9:44

Tim BiegeleisenTim Biegeleisen

254k1313 gold badges106106 silver badges166166 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Will a explain show an index scan ? Can a do it on a sample (the last index I created took 5 hours)?
  
  – guigoz
  Mar 25 at 15:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, you may try using a smaller sample of your data.
  
  – Tim Biegeleisen
  Mar 25 at 23:32
  

  
  
  
  

add a comment | 

This is a tough one, because you are querying against just a single table. I can suggest the following index:

CREATE INDEX idx ON my_table (col1, col2, col3, col4);

MySQL might choose to use this index, on the grounds that for every (col1, col2) group it can do an index scan to find each value of col3, and then concatenate together the distinct values of col4.

share|improve this answer

answered Mar 25 at 9:44

Tim BiegeleisenTim Biegeleisen

254k1313 gold badges106106 silver badges166166 bronze badges

CREATE INDEX idx ON my_table (col1, col2, col3, col4);

share|improve this answer

answered Mar 25 at 9:44

Tim BiegeleisenTim Biegeleisen

254k1313 gold badges106106 silver badges166166 bronze badges

answered Mar 25 at 9:44

Tim BiegeleisenTim Biegeleisen

254k1313 gold badges106106 silver badges166166 bronze badges

answered Mar 25 at 9:44

Tim BiegeleisenTim Biegeleisen

254k1313 gold badges106106 silver badges166166 bronze badges

0

(Please use real column names; often there are useful clues there.)

Maybe this will be faster...

First, let's see how fast it is to do all the GROUP_CONCATs at once:

SELECT col3,
 GROUP_CONCAT(DISTINCT col4) AS list
 FROM my_table
 GROUP BY col3;

That will take a full table scan (290M rows), but it can be sped up with

INDEX(col3, col4) -- in this order

which is 'covering'.

However, since you have col1 and col2 muddying the works, let's change to

SELECT col1, col2, col3,
 GROUP_CONCAT(DISTINCT col4) AS list
 FROM my_table
 GROUP BY col1, col3, col3;

and

INDEX(col1, col2, col3, col4) -- in this order

At that point, you have all the data but need to "pivot" it. (See the [pivot] tag.)

share|improve this answer

answered Apr 17 at 3:17

Rick JamesRick James

74.9k55 gold badges6868 silver badges110110 bronze badges

add a comment | 

0

(Please use real column names; often there are useful clues there.)

Maybe this will be faster...

First, let's see how fast it is to do all the GROUP_CONCATs at once:

SELECT col3,
 GROUP_CONCAT(DISTINCT col4) AS list
 FROM my_table
 GROUP BY col3;

That will take a full table scan (290M rows), but it can be sped up with

INDEX(col3, col4) -- in this order

which is 'covering'.

However, since you have col1 and col2 muddying the works, let's change to

SELECT col1, col2, col3,
 GROUP_CONCAT(DISTINCT col4) AS list
 FROM my_table
 GROUP BY col1, col3, col3;

and

INDEX(col1, col2, col3, col4) -- in this order

At that point, you have all the data but need to "pivot" it. (See the [pivot] tag.)

share|improve this answer

answered Apr 17 at 3:17

Rick JamesRick James

74.9k55 gold badges6868 silver badges110110 bronze badges

add a comment | 

(Please use real column names; often there are useful clues there.)

Maybe this will be faster...

First, let's see how fast it is to do all the GROUP_CONCATs at once:

SELECT col3,
 GROUP_CONCAT(DISTINCT col4) AS list
 FROM my_table
 GROUP BY col3;

That will take a full table scan (290M rows), but it can be sped up with

INDEX(col3, col4) -- in this order

which is 'covering'.

However, since you have col1 and col2 muddying the works, let's change to

SELECT col1, col2, col3,
 GROUP_CONCAT(DISTINCT col4) AS list
 FROM my_table
 GROUP BY col1, col3, col3;

and

INDEX(col1, col2, col3, col4) -- in this order

At that point, you have all the data but need to "pivot" it. (See the [pivot] tag.)

share|improve this answer

answered Apr 17 at 3:17

Rick JamesRick James

74.9k55 gold badges6868 silver badges110110 bronze badges

SELECT col3,
 GROUP_CONCAT(DISTINCT col4) AS list
 FROM my_table
 GROUP BY col3;

INDEX(col3, col4) -- in this order

SELECT col1, col2, col3,
 GROUP_CONCAT(DISTINCT col4) AS list
 FROM my_table
 GROUP BY col1, col3, col3;

INDEX(col1, col2, col3, col4) -- in this order

share|improve this answer

answered Apr 17 at 3:17

Rick JamesRick James

74.9k55 gold badges6868 silver badges110110 bronze badges

answered Apr 17 at 3:17

Rick JamesRick James

74.9k55 gold badges6868 silver badges110110 bronze badges

answered Apr 17 at 3:17

Rick JamesRick James

74.9k55 gold badges6868 silver badges110110 bronze badges