Mysql select query takes more time

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 403,516 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,884 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MySQL advertiser: Programming Forums

Thread Tools Display Modes

Views: 619 | Replies: 7

•

Join Date: Jul 2008

Posts: 5

Reputation: dhandapanik is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

dhandapanik dhandapanik is offline

Offline

Newbie Poster

Mysql select query takes more time

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

•

Join Date: Dec 2007

Posts: 112

Reputation: mwasif is an unknown quantity at this point

Rep Power: 1

Solved Threads: 13

mwasif

Offline

Junior Poster

Re: Mysql select query takes more time

Jul 7th, 2008

Did you EXPLAIN your query? What does it say?

•

Join Date: Jul 2008

Posts: 5

Reputation: dhandapanik is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

dhandapanik dhandapanik is offline

Offline

Newbie Poster

Re: Mysql select query takes more time

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.

Help with Code Tags

(Toggle Plain Text)

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)

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)

Last edited by peter_budo : Jul 8th, 2008 at 12:12 pm. Reason: Keep It Organized - please use [code] tags

•

Join Date: Jun 2008

Posts: 41

Reputation: varmadba is an unknown quantity at this point

Rep Power: 1

Solved Threads: 6

varmadba

Offline

Light Poster

Re: Mysql select query takes more time

Jul 8th, 2008

Help with Code Tags

(Toggle Plain Text)

 select count(*) from tblsendout 
         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

Please Join My Mysql Community:
http://www.orkut.com/Community.aspx?cmm=52345386

•

Join Date: Jul 2008

Posts: 5

Reputation: dhandapanik is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

dhandapanik dhandapanik is offline

Offline

Newbie Poster

Re: Mysql select query takes more time

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

•

Join Date: Jun 2008

Posts: 41

Reputation: varmadba is an unknown quantity at this point

Rep Power: 1

Solved Threads: 6

varmadba

Offline

Light Poster

Re: Mysql select query takes more time

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

Please Join My Mysql Community:
http://www.orkut.com/Community.aspx?cmm=52345386

•

Join Date: Jul 2008

Posts: 5

Reputation: dhandapanik is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

dhandapanik dhandapanik is offline

Offline

Newbie Poster

Re: Mysql select query takes more time

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

•

Join Date: Jul 2008

Posts: 5

Reputation: dhandapanik is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

dhandapanik dhandapanik is offline

Offline

Newbie Poster

Re: Mysql select query takes more time

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