Announcement

Announcement Module
Collapse
No announcement yet.

Mysql select query takes more time

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Mysql select query takes more time

    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

  • #2
    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.

    Comment


    • #3
      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_bran chid,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.par entid | 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

      Comment


      • #4
        And also I found that only 40% of CPU is used at all time & not more than that. Is there any way that I can configure such that it can use more CPU at a time?

        Regards,
        pani

        Comment


        • #5
          Hi Mikhail,
          Any updates?

          Regards,
          pani

          Comment

          Working...
          X