MYSQL query

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"      ""
mikestevenAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
markloganConnect With a Mentor Commented:
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
 
max-hbCommented:
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
 
mikestevenAuthor Commented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
mikestevenAuthor Commented:
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
 
max-hbCommented:
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
 
markloganCommented:
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
 
FugasCommented:
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
 
mikestevenAuthor Commented:
solution was not complete
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.