首先声明:
FACT_DATE_SALES_CI_INDEX 的DAY,DIM_ORG建有复合索引,同时FACT_DATE_SALES_FACT_DATE_SALES_F1表的RETAIL_SALES_DATE,DIM_ORG列上也建有复合索引
1、以内联视图的写法关联FACT_DATE_SALES_FACT_DATE_SALES_F1表,如下
mysql> explain
-> SELECT *
-> FROM FACT_DATE_SALES_CI_INDEX A
-> LEFT JOIN (SELECT MODEL_CODE, RETAIL_SALES_DATE, DIM_ORG, FLAG
-> FROM FACT_DATE_SALES_FACT_DATE_SALES_F1) B
-> ON A.DAY = B.RETAIL_SALES_DATE
-> AND A.DIM_ORG = B.DIM_ORG
-> LIMIT 20;
+----+-------------+------------------------------------+------+---------------+-------------+---------+-------------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra