Solved

MYSQL query

Posted on 2010-11-16
8
347 Views
Last Modified: 2012-06-21
Need help in speeding up the query below - table contains about 22 million rows.  I have tried waiting for more than an hour and still don't have the result which should be about 30 rows.  I have also tried indexing every field possible not sure what i'm doing wrong.

EXPLAIN SELECT CALLDATE,
SUM(IF(CALLRESULT='FAILED', 0, 1)) AS Failed,
SUM(IF(CALLRESULT='MACHINE', 1, 0)) AS Machine,
SUM(IF(CALLRESULT='MESSAGE', 1, 0)) AS Message,
SUM(IF(EXTENDEDRESULT='RIGHT PARTY URGENCY PAYMENT', 1, 0)) AS Payment,
SUM(IF(EXTENDEDRESULT='RIGHT PARTY', 1, 0)) AS RightParty
FROM disposition
GROUP BY CALLDATE

"id"      "select_type"      "table"      "type"      "possible_keys"      "key"      "key_len"      "ref"      "rows"      "Extra"
"1"      "SIMPLE"      "disposition"      "index"      \N      "ndx_disp_date_name"      "96"      \N      "22149253"      ""
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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 4

Expert Comment

by:max-hb
ID: 34145583
I believe your indexes are not usefull for calculating sums. Try the following sql code which should make full use of your indexes:
SELECT DISTINCT D.CALLDATE,
(select count(*) from CALLDATE D1 where D1.CALLDATE = D.CALLDATE and D1.CALLRESULT='FAILED') as Failed,
(select count(*) from CALLDATE D2 where D2.CALLDATE = D.CALLDATE and D2.CALLRESULT='MACHINE') as Machine,
(select count(*) from CALLDATE D3 where D3.CALLDATE = D.CALLDATE and D3.CALLRESULT='MESSAGE') as Message,
(select count(*) from CALLDATE D4 where D4.CALLDATE = D.CALLDATE and D4.EXTENDEDRESULT='RIGHT PARTY URGENCY PAYMENT') as Payment,
(select count(*) from CALLDATE D4 where D4.CALLDATE = D.CALLDATE and D4.EXTENDEDRESULT='RIGHT PARTY') as RightParty
FROM disposition D

Open in new window

0
 

Author Comment

by:mikesteven
ID: 34146247
i'm just new at MYSQL - got the following error below when i tried your query.

Query : SELECT DISTINCT D.CALLDATE,  (select count(*) from CALLDATE D1 where D1.CALLDATE = D.CALLDATE and D1.CALLRESULT='FAILED') as Fai...
Error Code : 1146
Table 'sandbox.calldate' doesn't exist
Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000
0
 
LVL 7

Accepted Solution

by:
marklogan earned 500 total points
ID: 34146574
You are selecting everything from a 22 million row table it will take a while.

Have you tried the same query but specifying a CALLDATE?

Insert a

WHERE CALLDATE = 'SOME DATE'

and see if an index is used. The only problem is though when you remove the date you will be selecting the whole table again.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:mikesteven
ID: 34146700
I need the data for all the dates on the table :-)

End result something to look something like below.

CALLDATE   Failed   Machine   Message   ...
10-01-2001   10        5                3
10-02-2001    2         0                1
10-03-2001    0         0                0
.
.
10-31-2001   0          0                0
0
 
LVL 4

Expert Comment

by:max-hb
ID: 34146761
Sorry, I don't understand. Your results look like you get info on each date...
Where is the problem? Can you post the fixed sql statement, please? Your table structure may be usefull as well (create table command).
0
 
LVL 7

Expert Comment

by:marklogan
ID: 34146780
That's an awkward date format to work with.

You could break the query down into multiple queries, a month at a time.


WHERE
 CALLDATE LIKE '2010-01%'

WHERE
 CALLDATE LIKE '2010-02%'

That's if you have CALLDATE indexed. Also is everything in the 2001 year?

Or if it is 10 years of history you could split the query by year

WHERE
 CALLDATE LIKE '%-2001'

WHERE
 CALLDATE LIKE '%-2002'

and then just group the results together manually.
0
 
LVL 4

Expert Comment

by:Fugas
ID: 34154551
Hi, the best solution is to use mysql 5.1 best on linux platform. You should create daily partitions and use some parallelism. Read this article first and I think everything would be clear for you(http://dev.mysql.com/doc/refman/5.1/en/partitioning-overview.html) . The second options is to build indexes with more columns like (COLLDATE, FAILED), (COLLDATE,MACHINE)... and grab values that you'd like to select directly from this indexes. But be sure in this case your insert queries will be much more slowlier and I really don't suggest this option to you.
0
 

Author Closing Comment

by:mikesteven
ID: 34247028
solution was not complete
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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
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 …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

732 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