Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MYSQL query

Posted on 2010-11-16
8
Medium Priority
?
351 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article, we’ll look at how to deploy ProxySQL.
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 Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

596 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