I'm working on a DWH and I'm so new to php and mySQL. I have a fact table with 6 indexes on it, and I have 6 dimension table. The dimension tables have two fields, ID and Value. The fact table has an ID as a primary key, and it has the ID of all dimension tables as foreign key. It also have some other fields. I need to join the fact table with dimension tables to get the value of all dimension tables. There are lots of records in the fact table, and when I query on it, execution takes long time. I use simple join query on these tables. Can anybody help me and say how should I query on a table using index to increase the performance? The query is as follows:
select * from dw.bohran_fct_etelaatenavegankhodroyi
INNER JOIN (select NameDastgahID as e1NameDastgahID, NameDastgahTitle from dw.excel_dim_namedastgah) e1 on (bohran_fct_etelaatenavegankhodroyi.NameDastgahID=e1NameDastgahID)
INNER JOIN (select NameKhodroID as e2NameKhodroID, NameKhodroTitle from dw.excel_dim_namekhodro) e2 on (bohran_fct_etelaatenavegankhodroyi.NameKhodroID=e2NameKhodroID)
INNER JOIN (select NoeNavganDastgahID as e3NoeNavganDastgahID, NoeNavganDastgahTitle from dw.excel_dim_noenavgandastgah) e3 on (bohran_fct_etelaatenavegankhodroyi.NoeNavganID=e3NoeNavganDastgahID)
INNER JOIN (select KarbarieKhodroID as e4KarbarieKhodroID, KarbarieKhodroTitle from dw.excel_dim_karbariekhodro) e4 on (bohran_fct_etelaatenavegankhodroyi.KarbariID=e4KarbarieKhodroID)
INNER JOIN (select ShahreKhodroID as e5ShahreKhodroID, ShahreKhodroTitle from dw.excel_dim_shahrekhodro) e5 on (bohran_fct_etelaatenavegankhodroyi.ShahrID=e5ShahreKhodroID) where 1=1
Finally I found the way for doing this query in a very short time! The query is:
select distinct (B.NameDastgahID), B.ZarfiateHmaleBar, B.ShomarePelak, B1.NameDastgahTitle, B2.NameKhodroTitle, B3.NoeKhodroTitle, B4.KarbarieKhodroTitle, B5.ShahreKhodroTitle
from dw.bohran_fct_etelaatenavegankhodroyi B
INNER JOIN dw.excel_dim_namedastgah B1 using (NameDastgahID)
INNER JOIN dw.excel_dim_namekhodro B2 using (NameKhodroID)
INNER JOIN dw.excel_dim_noenavgandastgah) B3 using (NoeKhodroID)
INNER JOIN dw.excel_dim_karbariekhodro) B4 using (KarbarieKhodroID)
INNER JOIN dw.excel_dim_shahrekhodro) B5 using (ShahreKhodroID)
where 1=1
It should be noted that the name of the fields in the dimension tables B1, B2, B3, B4, B5 (which are in the parentheses after using) should be the same as the name of fields in the fact table B.