Hi,
I am working in a finance project where each transaction table is having more than 4 Million records just for 3 months. I have user & branch related information on other tables.
I wrote a joined query by joining around 5 tables to summarize the report based on Branch, Time & type of transaction. But when I tried to run the query it is taking quite long time & sometime results in no response. timeout happens in servlet some time. I am using index for all necessary columns.
I am using JSP/servlet concept. And I am using Amazon EC2 small instance for running the jboss server.
I am really struggling with this for more than 2 weeks. Is there any way to improve the query performance?
I was advised to read the database into memory. But I do not know how to achieve this. Does anybody have an idea how to achieve his in java? Please help me.
At first it would be great to understand, what is the problem - java application, or MySQL.
Can you please run this query directly in MySQL client?
Also please run it with 'explain ’ before query, and post results here.
I guess te problem is with MySQL. There are around 4 Million records in each table.
Here is my explain select query response.(I have attached the same in word doc). I am new to join queries. I will be thankful if you can suggest solutions on this.
mysql> explain select L1.prov, ((100 * count(S.curprincipal))/(select count() from tblsendout where S.dtfiled between ‘2008-01-01 00:00’ and ‘2008-01-30 23:59’)), ROUND(avg(S.curprincipal)), ROUND(max(S.curprincipal)), ROUND(min(S.curprincipal)), ROUND(max(S.curprincipal)-min(S.curprincipal)), ROUND(sum(S.curprincipal)) as dispValue , count() from tblsendout S, tblpayout P, tblbranch B1, tblbranch B2, branch_locations L1, branch_locations L2, tbldistance D where S.objid=P.strsendoutid and S.strbranchid=B1.objid and P.strbranchid=B2.objid and B1.parentid=L1.id and B2.parentid=L2.id and L1.id=D.sourcebranch and L2.id=D.destinationbranch and S.dtfiled between ‘2008-01-01 00:00’ and ‘2008-01-30 23:59’ group by L1.prov order by dispValue desc limit 0, 10;
±—±-------------------±-----------±-------±---------- -----------------------------------------------------±----- ------------------±--------±------------------------------ --------------------------±--------±---------------------- ----------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±-------------------±-----------±-------±---------- -----------------------------------------------------±----- ------------------±--------±------------------------------ --------------------------±--------±---------------------- ----------+
| 1 | PRIMARY | B2 | ALL | PRIMARY,parentid | NULL | NULL | NULL | 1116 | Using temporary; Using filesort |
| 1 | PRIMARY | L2 | eq_ref | PRIMARY | PRIMARY | 4 | mlkp_statistics.B2.parentid | 1 | Using index |
| 1 | PRIMARY | P | ref | strsendoutid,idx_tblpayout_branchid,strbranchid | idx_tblpayout_branchid | 5 | mlkp_statistics.B2.objid | 3849 | Using where |
| 1 | PRIMARY | S | eq_ref | PRIMARY,idx_tblsendout_dtfiled,idx_tblsendout_branchid,ix_db us | PRIMARY | 8 | mlkp_statistics.P.strsendoutid | 1 | Using where |
| 1 | PRIMARY | B1 | eq_ref | PRIMARY,parentid | PRIMARY | 4 | mlkp_statistics.S.strbranchid | 1 | |
| 1 | PRIMARY | D | eq_ref | PRIMARY,sourcebranch,destinationbranch | PRIMARY | 8 | mlkp_statistics.B1.parentid,mlkp_statistics.B2.parentid | 1 | Using index |
| 1 | PRIMARY | L1 | eq_ref | PRIMARY | PRIMARY | 4 | mlkp_statistics.B1.parentid | 1 | |
| 2 | DEPENDENT SUBQUERY | tblsendout | index | NULL | idx_tblsendout_state | 3 | NULL | 4140800 | Using where; Using index |
±—±-------------------±-----------±-------±---------- -----------------------------------------------------±----- ------------------±--------±------------------------------ --------------------------±--------±---------------------- ----------+
8 rows in set (0.36 sec)