Solved

MYSQL Slow Query takes forever

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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…

762 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