Solved

MYSQL Slow Query takes forever

Posted on 2010-11-10
4
527 Views
Last Modified: 2012-05-10
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
Comment
Question by:mikesteven
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 25

Accepted Solution

by:
jrb1 earned 250 total points
ID: 34105772
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
 
LVL 8

Assisted Solution

by:wolfgang_93
wolfgang_93 earned 250 total points
ID: 34107873
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34114369
>>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
 

Author Closing Comment

by:mikesteven
ID: 34144551
was not the solution to the problem
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question