Ggtcf

Why can't we use freedom of speech and expression to incite people to rebel against government in India?

Giving a talk in my old university, how prominently should I tell students my salary?

Is "cogitate" an appropriate word for this?

Under what conditions would I NOT add my Proficiency Bonus to a Spell Attack Roll (or Saving Throw DC)?

Why doesn't "adolescent" take any articles in "listen to adolescent agonising"?

Has a sovereign Communist government ever run, and conceded loss, on a fair election?

Why would the IRS ask for birth certificates or even audit a small tax return?

How spaceships determine each other's mass in space?

In the world of The Matrix, what is "popping"?

Integrating function with /; in its definition

What's the best tool for cutting holes into duct work?

Are small insurances worth it

What is Tony Stark injecting into himself in Iron Man 3?

Quitting employee has privileged access to critical information

Why do phishing e-mails use faked e-mail addresses instead of the real one?

Can a space-faring robot still function over a billion years?

Computing the volume of a simplex-like object with constraints

What is the purpose of a disclaimer like "this is not legal advice"?

Iron deposits mined from under the city

The (Easy) Road to Code

Align equations with text before one of them

Sundering Titan and basic normal lands and snow lands

What is better: yes / no radio, or simple checkbox?

Learning to quickly identify valid fingering for piano?

myquery taking too long time in mysql

1

My transactionDetail table has 300 million data.For getting data for my query its taking very long time.

Below is my query

 select 
 MerchantId as y_m,BoothId ,
 TransactionTypeId , 
 count(Amount) , 
 sum(Amount) 
 from TransactionDetail 
 where TransactionDate>='2014-02-26' 
 and TransactionDate<'2019-02-27' 
 and not (BoothId like 'TEST%') 
 and MerchantId in (select MerchantId from MerchantGroup where MerchantClassId='MD-SAFAL') 
 group by MerchantId, BoothId, TransactionTypeId 
 order by y_m asc, BoothId asc, TransactionTypeId asc;

TransactionDetail table has below keys and indexes

• PRIMARY KEY (TransactionId),

Indexes are below

• KEY idxTransactionDetail003 (MerchantId),




• KEY idxTransactionDetail004 (TransactionDate)

MerchantGroup table has index on MerchantId column

mysql

share|improve this question

edited yesterday

James Z

11.2k71935

asked yesterday

Mahender MMahender M

62

New contributor

Mahender M is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Welcome to stackoverflow. There are lots of questions on query optimizatiopn here - do take some time to read some of them. Note which ones are upvoted or downvoted/closed and the reasons for this. Note which ones have been given useful answers. You have described some of the schema involved in the query (but not all) but have provided no details on the data distribution nor the metrics. Your starting point for looking at query performance should always be the EXPLAIN plan.
  
  – symcbean
  yesterday
  

  
  
  
  

add a comment | 

1

My transactionDetail table has 300 million data.For getting data for my query its taking very long time.

Below is my query

 select 
 MerchantId as y_m,BoothId ,
 TransactionTypeId , 
 count(Amount) , 
 sum(Amount) 
 from TransactionDetail 
 where TransactionDate>='2014-02-26' 
 and TransactionDate<'2019-02-27' 
 and not (BoothId like 'TEST%') 
 and MerchantId in (select MerchantId from MerchantGroup where MerchantClassId='MD-SAFAL') 
 group by MerchantId, BoothId, TransactionTypeId 
 order by y_m asc, BoothId asc, TransactionTypeId asc;

TransactionDetail table has below keys and indexes

• PRIMARY KEY (TransactionId),

Indexes are below

• KEY idxTransactionDetail003 (MerchantId),




• KEY idxTransactionDetail004 (TransactionDate)

MerchantGroup table has index on MerchantId column

mysql

share|improve this question

edited yesterday

James Z

11.2k71935

asked yesterday

Mahender MMahender M

62

New contributor

Mahender M is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Welcome to stackoverflow. There are lots of questions on query optimizatiopn here - do take some time to read some of them. Note which ones are upvoted or downvoted/closed and the reasons for this. Note which ones have been given useful answers. You have described some of the schema involved in the query (but not all) but have provided no details on the data distribution nor the metrics. Your starting point for looking at query performance should always be the EXPLAIN plan.
  
  – symcbean
  yesterday
  

  
  
  
  

add a comment | 

My transactionDetail table has 300 million data.For getting data for my query its taking very long time.

Below is my query

 select 
 MerchantId as y_m,BoothId ,
 TransactionTypeId , 
 count(Amount) , 
 sum(Amount) 
 from TransactionDetail 
 where TransactionDate>='2014-02-26' 
 and TransactionDate<'2019-02-27' 
 and not (BoothId like 'TEST%') 
 and MerchantId in (select MerchantId from MerchantGroup where MerchantClassId='MD-SAFAL') 
 group by MerchantId, BoothId, TransactionTypeId 
 order by y_m asc, BoothId asc, TransactionTypeId asc;

TransactionDetail table has below keys and indexes

• PRIMARY KEY (TransactionId),

Indexes are below

• KEY idxTransactionDetail003 (MerchantId),




• KEY idxTransactionDetail004 (TransactionDate)

MerchantGroup table has index on MerchantId column

mysql

share|improve this question

edited yesterday

James Z

11.2k71935

asked yesterday

Mahender MMahender M

62

New contributor

Mahender M is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

 select 
 MerchantId as y_m,BoothId ,
 TransactionTypeId , 
 count(Amount) , 
 sum(Amount) 
 from TransactionDetail 
 where TransactionDate>='2014-02-26' 
 and TransactionDate<'2019-02-27' 
 and not (BoothId like 'TEST%') 
 and MerchantId in (select MerchantId from MerchantGroup where MerchantClassId='MD-SAFAL') 
 group by MerchantId, BoothId, TransactionTypeId 
 order by y_m asc, BoothId asc, TransactionTypeId asc;

share|improve this question

edited yesterday

James Z

11.2k71935

asked yesterday

Mahender MMahender M

62

New contributor

Mahender M is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited yesterday

James Z

11.2k71935

asked yesterday

Mahender MMahender M

62

New contributor

Mahender M is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

edited yesterday

James Z

11.2k71935

edited yesterday

James Z

11.2k71935

asked yesterday

Mahender MMahender M

62

New contributor

Mahender M is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked yesterday

Mahender MMahender M

62

1 Answer
1

active

oldest

votes

1

you could add a composite index on TransactionDetail columns ( MerchantId, TransactionDate, BoothId )

and you could use a inner join instead of IN clause

and use not like instead of not ( like )

 select d.MerchantId as y_m
 ,d.BoothId 
 ,d.TransactionTypeId 
 , count(d.Amount) , sum(d.Amount) 
 from TransactionDetail d
 INNER JOIN (
 select MerchantId 
 from MerchantGroup 
 where MerchantClassId='MD-SAFAL'
 ) t t.MerchantId = d.MerchantId
 where d.TransactionDate>='2014-02-26' 
 and d.TransactionDate<'2019-02-27' 
 and d.BoothId not like 'TEST%' 
 group by d.MerchantId, d.BoothId, d.TransactionTypeId 
 order by y_m asc, d.BoothId asc, d.TransactionTypeId asc;

share|improve this answer

answered yesterday

scaisEdgescaisEdge

95.7k105072

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



);

Mahender M is a new contributor. Be nice, and check out our Code of Conduct.

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%2f55023000%2fmyquery-taking-too-long-time-in-mysql%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

you could add a composite index on TransactionDetail columns ( MerchantId, TransactionDate, BoothId )

and you could use a inner join instead of IN clause

and use not like instead of not ( like )

 select d.MerchantId as y_m
 ,d.BoothId 
 ,d.TransactionTypeId 
 , count(d.Amount) , sum(d.Amount) 
 from TransactionDetail d
 INNER JOIN (
 select MerchantId 
 from MerchantGroup 
 where MerchantClassId='MD-SAFAL'
 ) t t.MerchantId = d.MerchantId
 where d.TransactionDate>='2014-02-26' 
 and d.TransactionDate<'2019-02-27' 
 and d.BoothId not like 'TEST%' 
 group by d.MerchantId, d.BoothId, d.TransactionTypeId 
 order by y_m asc, d.BoothId asc, d.TransactionTypeId asc;

share|improve this answer

answered yesterday

scaisEdgescaisEdge

95.7k105072

add a comment | 

1

you could add a composite index on TransactionDetail columns ( MerchantId, TransactionDate, BoothId )

and you could use a inner join instead of IN clause

and use not like instead of not ( like )

 select d.MerchantId as y_m
 ,d.BoothId 
 ,d.TransactionTypeId 
 , count(d.Amount) , sum(d.Amount) 
 from TransactionDetail d
 INNER JOIN (
 select MerchantId 
 from MerchantGroup 
 where MerchantClassId='MD-SAFAL'
 ) t t.MerchantId = d.MerchantId
 where d.TransactionDate>='2014-02-26' 
 and d.TransactionDate<'2019-02-27' 
 and d.BoothId not like 'TEST%' 
 group by d.MerchantId, d.BoothId, d.TransactionTypeId 
 order by y_m asc, d.BoothId asc, d.TransactionTypeId asc;

share|improve this answer

answered yesterday

scaisEdgescaisEdge

95.7k105072

add a comment | 

you could add a composite index on TransactionDetail columns ( MerchantId, TransactionDate, BoothId )

and you could use a inner join instead of IN clause

and use not like instead of not ( like )

 select d.MerchantId as y_m
 ,d.BoothId 
 ,d.TransactionTypeId 
 , count(d.Amount) , sum(d.Amount) 
 from TransactionDetail d
 INNER JOIN (
 select MerchantId 
 from MerchantGroup 
 where MerchantClassId='MD-SAFAL'
 ) t t.MerchantId = d.MerchantId
 where d.TransactionDate>='2014-02-26' 
 and d.TransactionDate<'2019-02-27' 
 and d.BoothId not like 'TEST%' 
 group by d.MerchantId, d.BoothId, d.TransactionTypeId 
 order by y_m asc, d.BoothId asc, d.TransactionTypeId asc;

share|improve this answer

answered yesterday

scaisEdgescaisEdge

95.7k105072

 select d.MerchantId as y_m
 ,d.BoothId 
 ,d.TransactionTypeId 
 , count(d.Amount) , sum(d.Amount) 
 from TransactionDetail d
 INNER JOIN (
 select MerchantId 
 from MerchantGroup 
 where MerchantClassId='MD-SAFAL'
 ) t t.MerchantId = d.MerchantId
 where d.TransactionDate>='2014-02-26' 
 and d.TransactionDate<'2019-02-27' 
 and d.BoothId not like 'TEST%' 
 group by d.MerchantId, d.BoothId, d.TransactionTypeId 
 order by y_m asc, d.BoothId asc, d.TransactionTypeId asc;

share|improve this answer

answered yesterday

scaisEdgescaisEdge

95.7k105072

answered yesterday

scaisEdgescaisEdge

95.7k105072

answered yesterday

scaisEdgescaisEdge

95.7k105072