MySQL RSS MySQL RSS

Mysql select query takes more time

Reply

•
•
Join Date: Jul 2008
Posts: 5
Reputation: dhandapanik is an unknown quantity at this point 
Solved Threads: 0
dhandapanik dhandapanik is offline Offline
Newbie Poster

Mysql select query takes more time

 
0
  #1
Jul 7th, 2008
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2007
Posts: 190
Reputation: mwasif is an unknown quantity at this point 
Solved Threads: 25
mwasif mwasif is offline Offline
Junior Poster

Re: Mysql select query takes more time

 
0
  #2
Jul 7th, 2008
Did you EXPLAIN your query? What does it say?
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2008
Posts: 5
Reputation: dhandapanik is an unknown quantity at this point 
Solved Threads: 0
dhandapanik dhandapanik is offline Offline
Newbie Poster

Re: Mysql select query takes more time

 
0
  #3
Jul 8th, 2008
Here is my explain select query response. I am new to join queries. I will be thankful if you can suggest solutions on this.

MySQL Syntax (Toggle Plain Text) 
  1. 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;
  2. +----+--------------------+------------+--------+----------- -----------------------------------------------------+------ ------------------+---------+------------------------------- --------------------------+---------+----------------------- ----------+
  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  4. +----+--------------------+------------+--------+----------- -----------------------------------------------------+------ ------------------+---------+------------------------------- --------------------------+---------+----------------------- ----------+
  5. | 1 | PRIMARY | B2 | ALL | PRIMARY,parentid | NULL | NULL | NULL | 1116 | USING temporary; USING filesort |
  6. | 1 | PRIMARY | L2 | eq_ref | PRIMARY | PRIMARY | 4 | mlkp_statistics.B2.parentid | 1 | USING index |
  7. | 1 | PRIMARY | P | ref | strsendoutid,idx_tblpayout_branchid,strbranchid | idx_tblpayout_branchid | 5 | mlkp_statistics.B2.objid | 3849 | USING WHERE |
  8. | 1 | PRIMARY | S | eq_ref | PRIMARY,idx_tblsendout_dtfiled,idx_tblsendout_branchid,ix_db us | PRIMARY | 8 | mlkp_statistics.P.strsendoutid | 1 | USING WHERE |
  9. | 1 | PRIMARY | B1 | eq_ref | PRIMARY,parentid | PRIMARY | 4 | mlkp_statistics.S.strbranchid | 1 | |
  10. | 1 | PRIMARY | D | eq_ref | PRIMARY,sourcebranch,destinationbranch | PRIMARY | 8 | mlkp_statistics.B1.parentid,mlkp_statistics.B2.parentid | 1 | USING index |
  11. | 1 | PRIMARY | L1 | eq_ref | PRIMARY | PRIMARY | 4 | mlkp_statistics.B1.parentid | 1 | |
  12. | 2 | DEPENDENT SUBQUERY | tblsendout | index | NULL | idx_tblsendout_state | 3 | NULL | 4140800 | USING WHERE; USING index |
  13. +----+--------------------+------------+--------+----------- -----------------------------------------------------+------ ------------------+---------+------------------------------- --------------------------+---------+----------------------- ----------+
  14. 8 rows IN SET (0.36 sec)
Last edited by peter_budo; Jul 8th, 2008 at 1:12 pm. Reason: Keep It Organized - please use [code] tags
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2008
Posts: 79
Reputation: varmadba is an unknown quantity at this point 
Solved Threads: 8
varmadba varmadba is offline Offline
Junior Poster in Training

Re: Mysql select query takes more time

 
0
  #4
Jul 8th, 2008
MySQL Syntax (Toggle Plain Text) 
  1.  SELECT count(*) FROM tblsendout 
  2.          WHERE S.dtfiled BETWEEN '2008-01-01 00:00' AND '2008-01-30 23:59')

sub query is the one scanning larger number of rows

is this column indexed "S.dtfiled"?

if possible go for Stored Procedure so you can run the inner query separately and store the o/p in a variable and reuse it
so the actual query will be faster
:- Varma

We are Happy to inform launch of a new site with loads of database related information Site offers wide range of functionality Forums,Blogs,Articles,Editorials and much more
http://www.sqllibrarian.info/
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2008
Posts: 5
Reputation: dhandapanik is an unknown quantity at this point 
Solved Threads: 0
dhandapanik dhandapanik is offline Offline
Newbie Poster

Re: Mysql select query takes more time

 
0
  #5
Jul 8th, 2008
Thanks varma. Yes. S.dtfiled is indexed. But even without that sub query it took long time.

I am using default mysql system variable configurations. Is there any other way that I can increase heap/cache memory which will help me to improve the performance.

Regards,
pani
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2008
Posts: 79
Reputation: varmadba is an unknown quantity at this point 
Solved Threads: 8
varmadba varmadba is offline Offline
Junior Poster in Training

Re: Mysql select query takes more time

 
0
  #6
Jul 9th, 2008
ya there are some variables that can speed up mysql that depends on

what type of storage engine u r using
MyIsam is upto 12 times faster than Innodb but heavy inserts and deletes may corrupt the MyIsam Type table

MyIsam loads only the index in to ram where innodb loads the data and the index so u can allocate more space to innodb than MyIsam

you can check Thread Cache,Sort Buffer,Table Cache and Read Rnd Buffer and set it as per your environment

There are many thing that can be done but over all the queries are the first place to start with

if possible post create table syntax and explain o/p of the query(after sub query is removed)

Try this Link it has good articles about Mysql Tunning
http://www.mysqlperformanceblog.com/
:- Varma

We are Happy to inform launch of a new site with loads of database related information Site offers wide range of functionality Forums,Blogs,Articles,Editorials and much more
http://www.sqllibrarian.info/
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2008
Posts: 5
Reputation: dhandapanik is an unknown quantity at this point 
Solved Threads: 0
dhandapanik dhandapanik is offline Offline
Newbie Poster

Re: Mysql select query takes more time

 
0
  #7
Jul 9th, 2008
Hi,
Thanks for you response. I will try changing the cache/heap related variables & update you the response.

I am using MyIsam storage engine. I am using this database only for scheduled report generation & not for insert, update & delete.

I will try the above & see if am seeing any performance effect.

Regards,
pani
Reply With Quote Quick reply to this message  
•
•
Join Date: Jul 2008
Posts: 5
Reputation: dhandapanik is an unknown quantity at this point 
Solved Threads: 0
dhandapanik dhandapanik is offline Offline
Newbie Poster

Re: Mysql select query takes more time

 
0
  #8
Jul 9th, 2008
Thanks varma. I have one more question for you. Please suggest me the best one.


We have the choice of the following instance configurations:

7 GB of memory, 20 EC2 Compute Units (8 virtual cores with 2.5 EC2
Compute Units each), 1690 GB of instance storage, 64-bit platform

OR

15 GB of memory, 8 EC2 Compute Units (4 virtual cores with 2 EC2
Compute Units each), 1690 GB of instance storage, 64-bit platform

Please let me know which one is best. More CPU or More Memory?

Regards,
pani
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the MySQL Forum
Thread Tools Search this Thread



agplv3 alfresco amazon api artisticlicense aws bizspark breathalyzer camparingtocolumns changingprices cmg communityjournalism contentmanagement contractors copyright count court crm database design developer development distinct drupal ec2 email enterprise eudora facebook form foss gartner gnu gpl greenit groklaw groupware hiring hyperic images innerjoins insert ip joebrockmeier join journalism keywords kickfire laptop law legal license licensing linux maintenance managing mariadb matchingcolumns metron micromanage microsoft microsoftexchange mindtouch montywidenius mozilla multiple music mysql mysqlcolumnupdating mysqldatetimeordermax() mysqlindex mysqlinternalqueries mysqlquery mysqlsearch news open-xchange opendatabasealliance opengovernment opensource oracle penelope php priceupdating query referencedesign reorderingcolumns resultset saas select sharepoint simpledb sourcecode spotify sql sugarcrm syntax techsupport thunderbird transparency virtualization
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC