Solved

MYSQL query

Posted on 2010-11-16
8
343 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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
User with DBO permission can't remotely connect 15 50
Insert data into database 2 42
when to use sequences in mysql 4 27
possible to record changes (trigger I think) msql 11 35
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Creating and Managing Databases with phpMyAdmin in cPanel.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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