MySQL Forums
Forum List  »  Quality Assurance

mySql query execution plan
Posted by: Carlos Bras
Date: December 07, 2012 03:52AM

Hello,

We are trying to build a small standard data mart model using ti kimball approach on a mySql database .. however the performace of the model is pretty slow.

After some investigation we found out that the performace issue its because mySql is not providing us the best execution plans.

Or model is as per following:

Create table fact
(date_id int
, dim1_id int
, dim2_id int
, dim3_id int
, measure1 int
, measure2 int)
partition by date_id
;
-- =~10000 per day/partition

Create table dim1
( dim1_id int
, dim1_name varchar(50 char)
, dim1_desc varchar(250 char)
)
;
-- 300 records

Create table dim2
( dim2_id int
, dim2_name varchar(50 char)
, dim2_desc varchar(250 char)
)
;
-- 50 records

Create table dim3
( dim3_id int
, dim3_name varchar(50 char)
, dim3_desc varchar(250 char)
)
;
-- 25 records

when we run the following query:

select t2.dim1_name, t3.dim2_name, t4.dim3_name, sum(t1.measure1)
from fact t1
inner join dim1 t2 on t1.dim1_id = t2.dim1_id
inner join dim2 t3 on t1.dim2_id = t3.dim2_id
inner join dim3 t4 on t1.dim3_id = t4.dim3_id
where t1.date_id = 20120401
and t1.dim1_id = 99
group by t2.dim1_name, t3.dim2_name, t4.dim3_name
;

the execution plan that we have is:

'1', 'SIMPLE', 't4', 'ALL', NULL, NULL, NULL, NULL, '25', 'Using temporary; Using filesort'
'1', 'SIMPLE', 't3', 'ALL', NULL, NULL, NULL, NULL, '50', 'Using join buffer'
'1', 'SIMPLE', 't2', 'ALL', NULL, NULL, NULL, NULL, '300', 'Using where; Using join buffer'
'1', 'SIMPLE', 't1', 'ALL', NULL, NULL, NULL, NULL, '10532', 'Using where; Using join buffer'

which it is far from being the best one .... since it's joining dim1 with dim2 with dim3 where there is no joining link ..

is there any way for us to manipulate the mySql resolver in order to get better execution plans ..
we were expecting it to return something like:

'1', 'SIMPLE', 't2', 'ALL', NULL, NULL, NULL, NULL, '300', 'Using where; Using join buffer'
'1', 'SIMPLE', 't1', 'ALL', NULL, NULL, NULL, NULL, '10532', 'Using where; Using join buffer'
'1', 'SIMPLE', 't4', 'ALL', NULL, NULL, NULL, NULL, '25', 'Using temporary; Using filesort'
'1', 'SIMPLE', 't3', 'ALL', NULL, NULL, NULL, NULL, '50', 'Using join buffer'

We've tried to play arround with the variable
- optimizer_prune_level
- optimizer_search_depth
- optimizer_switch

with no success.

thanks in advance for you help.

Regards,
Carlos

Options: ReplyQuote


Subject
Views
Written By
Posted
mySql query execution plan
2946
December 07, 2012 03:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.