?
Solved

MYSQL query

Posted on 2010-11-16
8
Medium Priority
?
349 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 1500 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

801 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