Query taking longer time

MySQL & MariaDB Other MySQL® Questions
1

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

2
[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]

3

Thanks for your suggestions.

Best Regards
Susheel