Solved

MYSQL Slow Query takes forever

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 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

19 Experts available now in Live!

Get 1:1 Help Now