3

I've tidied up my post and clarified a few things for the bounty.

Magento is generating two different select queries for the same category on my staging site and live site.

Scenario

  1. Backed up all files on live site - minus cache & sessions.
  2. Created a dump of my magento database.
  3. Setup staging site based on the backup files
  4. Visited: mylivesite.com/cat1 and viewed the results of (string)$_productCollection->getSelect() (below)
  5. Visited: mystagesite.com/cat1 and viewed the results of (string)$_productCollection->getSelect() (below)

Here is a comparison of the two SQL selects: https://www.diffnow.com/?report=zlg7h

Also, insignificant as it may be, my staging site is on an AWS RDS db.t2.small and my live site on db.m3.large.

Stage Output

SELECT 
`e`.*,
`cat_index`.`position` AS `cat_index_position`,
`price_index`.`price`,
`price_index`.`tax_class_id`,
`price_index`.`final_price`,
IF(price_index.tier_price IS NOT NULL,
    LEAST(price_index.min_price,
            price_index.tier_price),
    price_index.min_price) AS `minimal_price`,
`price_index`.`min_price`,
`price_index`.`max_price`,
`price_index`.`tier_price`,
IF(at_name.value_id > 0,
    at_name.value,
    at_name_default.value) AS `name`
FROM
    `mg_catalog_product_entity` AS `e`
INNER JOIN
    `mg_catalog_category_product_index` AS `cat_index` ON cat_index.product_id = e.entity_id
    AND cat_index.store_id = 1
    AND cat_index.visibility IN (2 , 4)
    AND cat_index.category_id = '4'
    INNER JOIN
`mg_catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id
    AND price_index.website_id = '1'
    AND price_index.customer_group_id = 0
    LEFT JOIN
`mg_catalog_product_entity_varchar` AS `at_name_default` ON (`at_name_default`.`entity_id` = `e`.`entity_id`)
    AND (`at_name_default`.`attribute_id` = '71')
    AND `at_name_default`.`store_id` = 0
    LEFT JOIN
`mg_catalog_product_entity_varchar` AS `at_name` ON (`at_name`.`entity_id` = `e`.`entity_id`)
    AND (`at_name`.`attribute_id` = '71')
    AND (`at_name`.`store_id` = 1)

Live Output

SELECT 
    `e`.`entity_id`,
    `e`.`type_id`,
    `e`.`attribute_set_id`,
    `cat_index`.`position` AS `cat_index_position`,
    `e`.`name`,
    `e`.`short_description`,
    `e`.`price`,
    `e`.`special_price`,
    `e`.`special_from_date`,
    `e`.`special_to_date`,
    `e`.`small_image`,
    `e`.`thumbnail`,
    `e`.`news_from_date`,
    `e`.`news_to_date`,
    `e`.`status`,
    `e`.`url_key`,
    `e`.`required_options`,
    `e`.`image_label`,
    `e`.`small_image_label`,
    `e`.`thumbnail_label`,
    `e`.`msrp_enabled`,
    `e`.`msrp_display_actual_price_type`,
    `e`.`msrp`,
    `e`.`tax_class_id`,
    `e`.`price_type`,
    `e`.`weight_type`,
    `e`.`price_view`,
    `e`.`shipment_type`,
    `e`.`links_purchased_separately`,
    `e`.`links_exist`,
    `e`.`equipment_needed`,
    `e`.`equipment_needed_value`,
    `e`.`garnish`,
    `e`.`glass_recommended`,
    `e`.`glass_recommended_value`,
    `e`.`howserved`,
    `e`.`howserved_value`,
    `e`.`servingsize`,
    `e`.`servingsize_value`,
    `e`.`bevtypeid`,
    `e`.`bevtypeid_value`,
    `e`.`is_healthy`,
    `e`.`is_healthy_value`,
    `e`.`giftcard_amounts`,
    `e`.`allow_open_amount`,
    `e`.`open_amount_min`,
    `e`.`open_amount_max`,
    `e`.`last_chance`,
    `e`.`last_chance_value`,
    `price_index`.`price`,
    `price_index`.`tax_class_id`,
    `price_index`.`final_price`,
    IF(price_index.tier_price IS NOT NULL,
        LEAST(price_index.min_price,
                price_index.tier_price),
        price_index.min_price) AS `minimal_price`,
    `price_index`.`min_price`,
    `price_index`.`max_price`,
    `price_index`.`tier_price`
FROM
    `mg_catalog_product_flat_1` AS `e`
        INNER JOIN
    `mg_catalog_category_product_index` AS `cat_index` ON cat_index.product_id = e.entity_id
        AND cat_index.store_id = 1
        AND cat_index.category_id = '4'
        INNER JOIN
    `mg_catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id
        AND price_index.website_id = '1'
        AND price_index.customer_group_id = 0
WHERE
    (e.status = 1)
        AND (e.entity_id IN ('6174' , '6568',
        '6511',
        '9370',
        '6436',
        '6304',
        '6760',
        '6742',
        '6456',
        '9906',
        '6558',
        '6461'))
  • Do you have flat product index enabled on your live site? – benmarks Aug 4 '15 at 16:40
  • 1
    Use Flat Catalog Category & Use Flat Catalog Product are set to yes on both. Live Setting: Category Flat Index, Catalog Search Index and Product Flat Index are set to Update when Scheduled. Stage Setting: Each index listed above is set to - Update on Save. – TylersSN Aug 4 '15 at 16:45
  • 1
    1) Ensure that the indexes are up to do date & clear EAV collection cache. 2) Ensure that attributes at_name and at_name_default are part of the flat tables on staging. – benmarks Aug 4 '15 at 16:55
  • 1
    I don't see at_name nor at_name_default in any of the tables listed in the query. – TylersSN Aug 4 '15 at 17:03
  • 1
    SELECT * FROM mg_eav_attribute WHERE attribute_code LIKE "at_nam%" should tell you if those attributes exist in EAV configuration. – benmarks Aug 4 '15 at 17:13
9
+25

if magento reads from the catalog_product_flat_* table means that the flat products flag is set to yes and it's up to date and it's not locked.
If it reads from the catalog_product_entity table it means that the flat products flag is set to no or it's not up to date or is locked.
As simple as that.
If you say that you cloned the db from live to staging it means that the flag has the same value on both instances. If that's true (and it is) the flag is set to Yes on both instances.
based on the facts listed above I conclude that the index on your staging is not up to date or the var/lock folder is not readable/writable.
Delete the contents of var/lock, give it read/write permisions and rebuild all the indexes on staging and you should get the same selects.
Now some technical stuff.
Magento knows how where to read from depending on the result of the method Mage_Catalog_Model_Resource_Product_Collection::isEnabledFlat().
The method looks like this:

public function isEnabledFlat()
{
    // Flat Data can be used only on frontend
    if (Mage::app()->getStore()->isAdmin()) {
        return false;
    }
    $storeId = $this->getStoreId();
    if (!isset($this->_flatEnabled[$storeId])) {
        $flatHelper = $this->getFlatHelper();
        $this->_flatEnabled[$storeId] = $flatHelper->isAvailable() && $flatHelper->isBuilt($storeId);
    }
    return $this->_flatEnabled[$storeId];
}

This means that if you are on the admin side flat tables are not used.
otherwise, the flat tables are used if $flatHelper->isAvailable() && $flatHelper->isBuilt($storeId) returns true.
Digging deeper in these 2 methods you will find that isAvailable returns true if the flag in the backend is set to yes or if the process is not running. and isBuild returns true if the reindexing is finished.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.