# Mysql select query takes more time **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [July 7, 2008, 1:58am UTC](https://forums.percona.com/t/mysql-select-query-takes-more-time/829 "2008-07-07T01:58:24Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![dhandapanik](https://avatars.discourse-cdn.com/v4/letter/d/90ced4/32.png) [@dhandapanik](https://forums.percona.com/u/dhandapanik)\ **Post date:** [July 7, 2008, 1:58am UTC](https://forums.percona.com/t/mysql-select-query-takes-more-time/829/1 "2008-07-07T01:58:24Z")
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. Regards, pani ---
**Author:** ![debug](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/debug/32/772_2.png) [@debug](https://forums.percona.com/u/debug)\ **Post date:** [July 7, 2008, 3:37am UTC](https://forums.percona.com/t/mysql-select-query-takes-more-time/829/2 "2008-07-07T03:37:33Z")
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. ---
**Author:** ![dhandapanik](https://avatars.discourse-cdn.com/v4/letter/d/90ced4/32.png) [@dhandapanik](https://forums.percona.com/u/dhandapanik)\ **Post date:** [July 7, 2008, 1:36pm UTC](https://forums.percona.com/t/mysql-select-query-takes-more-time/829/3 "2008-07-07T13:36:27Z")
Hi Thanks for your response. 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) Regards, pani