# Query taking longer time

**URL:** <https://forums.percona.com/t/query-taking-longer-time/653>\
**Category:** Other MySQL® Questions\
**Created:** [February 26, 2008, 11:15pm UTC](https://forums.percona.com/t/query-taking-longer-time/653 "2008-02-26T23:15:39Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![susheel](https://avatars.discourse-cdn.com/v4/letter/s/85f322/32.png) [@susheel](https://forums.percona.com/u/susheel)\
**Post date:** [February 26, 2008, 11:15pm UTC](https://forums.percona.com/t/query-taking-longer-time/653/1 "2008-02-26T23:15:39Z")

</div>

Hi all,

I am using mysql 5 on solaris 8.  
I am executing a query which is taking 34sec.  
Query

select `Dim_Hospital`.`cityOfHospital` as `c0`, `DO_Closure`.`parentId` as `c1`, sum(`Fact_ClaimTr`.`finalBillAmt`) as `m0`, count(`Fact_ClaimTr`.`pioId`) as `m1` from `Dim_Hospital` as `Dim_Hospital`, `Fact_ClaimTr` as `Fact_ClaimTr`, `DO_Closure` as `DO_Closure` where `Fact_ClaimTr`.`hospitalId` = `Dim_Hospital`.`id` and `Fact_ClaimTr`.`pioId` = `DO_Closure`.`insuOfficeId` and `DO_Closure`.`parentId` = ‘800001169443885743’ group by `Dim_Hospital`.`cityOfHospital`, `DO_Closure`.`parentId`;

explain

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* id: 1 select\_type: SIMPLE table: Dim\_Hospital type: ALLpossible\_keys: PRIMARY key: NULL key\_len: NULL ref: NULL rows: 33 Extra: Using temporary; Using filesort\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* id: 1 select\_type: SIMPLE table: Fact\_ClaimTr type: refpossible\_keys: hos\_Id,pio\_Id key: hos\_Id key\_len: 9 ref: claimanalytics.Dim\_Hospital.id rows: 5769 Extra: Using where\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 3. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* id: 1 select\_type: SIMPLE table: DO\_Closure type: refpossible\_keys: PRIMARY key: PRIMARY key\_len: 16 ref: claimanalytics.Fact\_ClaimTr.pioId,const rows: 1 Extra: Using index

Table :No Of Rows  
DO\_Closure : 206  
Fact\_ClaimTr : 190387  
Dim\_Hospital : 33

I have indexes on all columns in join and group by clause  
How can I reduce this time?  
Thanks for any suggestion you can provide.

Regards  
Susheel

---

<div class="post-metadata">

**Author:** ![dwellman](https://avatars.discourse-cdn.com/v4/letter/d/ecc23a/32.png) [@dwellman](https://forums.percona.com/u/dwellman)\
**Post date:** [February 27, 2008, 12:22pm UTC](https://forums.percona.com/t/query-taking-longer-time/653/2 "2008-02-27T12:22:11Z")

</div>

| [B]susheel wrote on Tue, 26 February 2008 23:45[/B] |
| Hi all,

I am using mysql 5 on solaris 8.  
I am executing a query which is taking 34sec.  
Query  
[…]  
I have indexes on all columns in join and group by clause  
How can I reduce this time?  
Thanks for any suggestion you can provide.

Regards  
Susheel

 |

any GROUP or ORDER is going to cause temporary table and a filesort, a generally slower operation. You might get better performance by increasing tmp\_table\_size, max\_heap\_table\_size (I make them equal).

Other than that there are a couple things that I think about when I want to male something run faster (in no particular order):

[LIST]  
[_] Joins preform better when the fields being joined are the same datatype-- well, its more important for character/text type fields (same Charset and Collation).  
[_] The fastest query is the one that doesn’t need to run; if not, anything complex can be broken into simpler pieces. Splitting a query into smaller pieces is a common recourse. Sometimes, throwing hardware at a problem is well and good. Q: How much memory is enough? A: More.  
[/LIST]

---

<div class="post-metadata">

**Author:** ![susheel](https://avatars.discourse-cdn.com/v4/letter/s/85f322/32.png) [@susheel](https://forums.percona.com/u/susheel)\
**Post date:** [February 27, 2008, 11:03pm UTC](https://forums.percona.com/t/query-taking-longer-time/653/3 "2008-02-27T23:03:47Z")

</div>

Thanks for your suggestions.

Best Regards  
Susheel