Difference execution plans for same query

[Devrishi Shandilya]

Hi,

I am facing very strang situation that execution plan of same query are different on prod and staging server. I want execution plan of staging need on prod.
I am using mysql community 5.6 on both machines, can anyone help me to figure out the root cause of this.

one more thing if i run analyze tables on staging server then execution plan will be same as prod but we do not want this.
We need to have execution plan on prod same as staging.


explain SELECT *
FROM sales_flat_invoice SFI
LEFT JOIN sales_flat_invoice_item SFII ON SFII.parent_id = SFI.entity_id
INNER JOIN sales_flat_order_item SFOI ON SFOI.item_id = SFII.order_item_id
INNER JOIN sales_flat_shipment_item AS SFSI ON SFSI.order_item_id=SFOI.item_id
INNER JOIN sales_flat_order SFO ON SFO.entity_id = SFI.order_id
INNER JOIN sales_flat_shipment AS SFS ON SFS.entity_id=SFSI.parent_id
INNER JOIN sales_flat_shipment_track AS SFST ON SFST.parent_id=SFS.entity_id
INNER JOIN sales_flat_order_payment AS SFOP ON SFOP.parent_id = SFO.entity_id
INNER JOIN catalog_product_entity_int AS CPEI ON CPEI.entity_id = SFOI.product_id
LEFT JOIN vendor_vendordirectory_vendors AS VVV ON CPEI.value= VVV.vendor_id AND CPEI.entity_type_id = 4
LEFT JOIN seller_admin_map SAM ON SAM.seller_id = VVV.user_id
WHERE 1 AND (SAM.admin_user_id IS NULL OR SAM.admin_user_id='24577')
AND (CPEI.attribute_id=133) AND SFOI.created_at BETWEEN DATE_SUB( "2015-12-01 23:59:59",INTERVAL 1 day) AND "2015-12-01 23:59:59" group by SFOI.item_id;




Execution plan on prod

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE SFI ALL PRIMARY,IDX_SALES_FLAT_INVOICE_ORDER_ID 491827 Using temporary; Using filesort
1 SIMPLE SFII ref IDX_SALES_FLAT_INVOICE_ITEM_PARENT_ID IDX_SALES_FLAT_INVOICE_ITEM_PARENT_ID 4 magento.SFI.entity_id 1 Using where
1 SIMPLE SFO eq_ref PRIMARY PRIMARY 4 magento.SFI.order_id 1
1 SIMPLE SFOP ref IDX_SALES_FLAT_ORDER_PAYMENT_PARENT_ID IDX_SALES_FLAT_ORDER_PAYMENT_PARENT_ID 4 magento.SFI.order_id 1
1 SIMPLE SFOI eq_ref PRIMARY,IDX_SALES_FLAT_ORDER_ITEM_ORDER_ID,IDX_SALES_FLAT_ORDER_ITEM_STORE_ID PRIMARY 4 magento.SFII.order_item_id 1 Using where
1 SIMPLE CPEI ref UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID 6 magento.SFOI.product_id,const 1
1 SIMPLE VVV eq_ref PRIMARY PRIMARY 4 magento.CPEI.value 1 Using where
1 SIMPLE SAM ALL FK_seller_admin_map_user 1 Using where; Using join buffer (Block Nested Loop)
1 SIMPLE SFST ALL IDX_SALES_FLAT_SHIPMENT_TRACK_PARENT_ID 455628 Using join buffer (Block Nested Loop)
1 SIMPLE SFS eq_ref PRIMARY PRIMARY 4 magento.SFST.parent_id 1
1 SIMPLE SFSI ref IDX_SALES_FLAT_SHIPMENT_ITEM_PARENT_ID IDX_SALES_FLAT_SHIPMENT_ITEM_PARENT_ID 4 magento.SFST.parent_id 1 Using where


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Execution plan on staging

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE SFST ALL IDX_SALES_FLAT_SHIPMENT_TRACK_PARENT_ID 444560 Using temporary; Using filesort
1 SIMPLE SFS eq_ref PRIMARY PRIMARY 4 magento.SFST.parent_id 1
1 SIMPLE SFSI ref IDX_SALES_FLAT_SHIPMENT_ITEM_PARENT_ID IDX_SALES_FLAT_SHIPMENT_ITEM_PARENT_ID 4 magento.SFST.parent_id 1 Using where
1 SIMPLE SFOI eq_ref PRIMARY,IDX_SALES_FLAT_ORDER_ITEM_ORDER_ID,IDX_SALES_FLAT_ORDER_ITEM_STORE_ID PRIMARY 4 magento.SFSI.order_item_id 1 Using where
1 SIMPLE SFII ref IDX_SALES_FLAT_INVOICE_ITEM_PARENT_ID,IDX_order_item_id IDX_order_item_id 5 magento.SFOI.item_id 1 Using index condition; Using where
1 SIMPLE SFI eq_ref PRIMARY,IDX_SALES_FLAT_INVOICE_ORDER_ID PRIMARY 4 magento.SFII.parent_id 1
1 SIMPLE SFO eq_ref PRIMARY PRIMARY 4 magento.SFI.order_id 1
1 SIMPLE SFOP ref IDX_SALES_FLAT_ORDER_PAYMENT_PARENT_ID IDX_SALES_FLAT_ORDER_PAYMENT_PARENT_ID 4 magento.SFI.order_id 1
1 SIMPLE CPEI ref UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID UNQ_CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID 6 magento.SFOI.product_id,const 1
1 SIMPLE VVV eq_ref PRIMARY PRIMARY 4 magento.CPEI.value 1 Using where
1 SIMPLE SAM ALL FK_seller_admin_map_user 1 Using where; Using join buffer (Block Nested Loop)

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------