mySql query execution plan
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