Mysqlexplain: innerjoinanalysis; & lt; eq_ref) betterthan &

Explain
Select t. order_sn, t. cust_code, ti. tms_order_other_info_id, sp. province_name, SC. city_name, sr. region_name, st. town_name, t. buyer_address
From tms_order t inner join tms_order_other_info ti on t. tms_order_id = ti. tms_order_id
Inner join crm_cust c on t. cust_code = c. cust_code
Left join sb_province sp on t. buyer_state = sp. province_code
Inner join sb_city SC on t. buyer_city = SC. city_code
Inner join sb_region sr on t. buyer_area_id = sr. region_code
Left join sb_town st on t. buy_town = st. town_code
Where t. created_office = 'vip _ NH 'and c. created_office = 'vip _ NH'
# And (t. cust_code = 'nhldp053 'or 'nhldp053' = '')
And t. is_autopicked in (1, 3)
And t. order_sub_type = 11
And t. order_status in (1, 2)
And t. cust_code is not null
And t. add_time> date_sub (now (), interval 10 day)
And c. is_showpoint = 1 and ifnull (ti. has_matchpoint, 0) = 0

Order by t. cust_code, t. created_dtm_loc limit 500

The table tms_order above contains tens of millions of data records.

The above execution efficiency is normal, and the Execution Plan is also normal,

After changing left join sb_province sp to inner join sb_province sp: the efficiency will decrease linearly.

Explain
Select t. order_sn, t. cust_code, ti. tms_order_other_info_id, sp. province_name, SC. city_name, sr. region_name, st. town_name, t. buyer_address
From tms_order t inner join tms_order_other_info ti on t. tms_order_id = ti. tms_order_id
Inner join crm_cust c on t. cust_code = c. cust_code
Inner join sb_province sp on t. buyer_state = sp. province_code
Inner join sb_city SC on t. buyer_city = SC. city_code
Inner join sb_region sr on t. buyer_area_id = sr. region_code
Left join sb_town st on t. buy_town = st. town_code
Where t. created_office = 'vip _ NH 'and c. created_office = 'vip _ NH'
# And (t. cust_code = 'nhldp053 'or 'nhldp053' = '')
And t. is_autopicked in (1, 3)
And t. order_sub_type = 11
And t. order_status in (1, 2)
And t. cust_code is not null
And t. add_time> date_sub (now (), interval 10 day)
And c. is_showpoint = 1 and ifnull (ti. has_matchpoint, 0) = 0
Order by t. cust_code, t. created_dtm_loc limit 500

The type of the sp table becomes ALL, And the execution efficiency decreases linearly. why ?????

Because of the inner join method, the optimization will execute the process as follows:

-> First, range analysis analyzes the approximate number of records of all tables. The sp table is the smallest of dozens of rows, which is used as the moving Table and placed on the leftmost, determines the order of the sp table-> calculate the I/O cost for full table scanning and indexing, and determine the access method of the sp table. No index is required, and what type of index is used, here it is used Ref Index,-> According to this sorting, the cost of their execution plan is analyzed by means of the exhaustive analysis. It can be seen that the sp table data is too small, and the cost of first join is the smallest, (it can also be understood that the optimizer generally does not need to index a table with a small amount of data in inner join)

In addition:

Except for the use of eq_ref indexes in the st table, the ref indexes are used in other tables. That is to say, the scanning of the st table is faster than that of other tables... why ???

Because the index referenced by the sp table is a uniqe key,

(Eq_ref: a row of records is read from the table and associated with the records read from the previous table .)

(Ref: All records that match the search value in the table will be retrieved and used together with the records obtained from the previous table .)

(Ref_or_null: This connection type is similar to ref. The difference is that mysql will search for records containing null values during retrieval. This connection type is optimized from mysql4.1.1 and is often used for subqueries .)

Efficiency eq_ref> ref> ref_or_null