Solved

MYSQL Slow Query takes forever

Posted on 2010-11-10
4
522 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
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parse field in SQL View 15 97
T-SQL:  I Want "Summary"--Not "Detail" 6 22
how to install/upgrade the Blitz responder kit 8 24
SQL Server stored proc 2 13
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.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now