• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 541
  • Last Modified:

MYSQL Slow Query takes forever

The issue with the query below is it takes forever (tried waiting for more than an hour) to finish - need help in finding a solution to speed it up.

SELECT Table1.A,
49*('Table1.F'-'Table1.E')/172800 AS PH,
('Table1.F'-'Table1.E')/3600 AS DH,
SUM(IF(‘Table3.D’='TD', 0, 1)) AS TD,
SUM(IF(‘Table3.D’='AM’, 1, 0)) AS AM,
SUM(IF(‘Table3.D’='LM', 1, 0)) AS LM,
Table1.D AS IT,
Table1.I AS TT,
Table1.P AS WT,
SUM(IF(‘Table3.E’='RPUP', 1, 0)) AS RPUP,
SUM(IF(‘Table3.E’='RP', 1, 0)) AS RP,
Table4.E AS IBCO,
Table2.I AS IBTT,
Table2.P AS IBWT,
Table4.H AS IBAB
FROM Table1 LEFT JOIN Table3
ON Table1.A=Table3.B
LEFT JOIN Table4
ON Table1.A=Table4.A
LEFT JOIN Table2
ON Table1.A=Table2.A
GROUP BY A

EXPLAIN
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
"1","SIMPLE","Table1","index",\N,"ind_table1_ab","95",\N,"1",""
"1","SIMPLE","Table3","ref","ind_table3_bf","ind_table3_bf","28","ee.Table1.a","1244294",""
"1","SIMPLE","Table4","ref","ind_table4_a","ind_table4_a","28","ee.Table1.a","1",""
"1","SIMPLE","Table2","ref","ind_table2_a","ind_table2_a","28","ee.Table1.a","51",""


0
mikesteven
Asked:
mikesteven
2 Solutions
 
jrb1Commented:
It doesn't look like you have enough fields in the group by.

You pull a row from Table 1 with a single value for "A".  Does this only have one row in each of the tables, Table2, Table3, Table4?  If multiple you will get each row multiple times.
0
 
wolfgang_93Commented:
You should make sure to index the A field for Table1, Table2, Table3, and for
Table4.

Reason: GROUP BY involves internal grouping/sorting and so benefits from
an index. JOINS also benefit from indexes. Without an index on a 4 way join
of say tables of say just 100 rows each involves the equivalent of
working with 100x100x100x100 = 100,000,000 rows of a single table.

As an aside, there is an issue with the SELECT statement itself as jrb1 has noted.

I believe there is a misunderstanding on how GROUP BY works. For each field or formula in the
select section -- if it is not listed in the "GROUP BY" section -- you should apply an aggregate
function such as SUM, MIN, MAX, etc. to be applied to each field value in the group to yield
a single value to be displayed each time A changes.

For example, you either need to apply an aggregate for the PH defined field -- something like
this for example:
   49*(SUM(Table1.F - Table1.E)/172800 AS PH

or you need to look at expanding your GROUP BY list to include a grouping by the
appropriate variable or variable-formula.

0
 
Anthony PerkinsCommented:
>>Zones: MySQL Server, MS SQL Server<<
Are you looking for a T-SQL solution?  If not, please don't post in the MS SQL Server zone, it is unrelated.
0
 
mikestevenAuthor Commented:
was not the solution to the problem
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now