Pasted

SELECT SQL_NO_CACHE COUNT(DISTINCT (entity_id))
FROM (SELECT e.entity_id
      FROM `catalog_product_flat_3` AS `e`
               INNER JOIN
           `catalog_category_product_index` AS `cat_index` ON cat_index.product_id = e.entity_id
               AND cat_index.store_id = 3
               AND cat_index.visibility IN (2, 4)
               AND cat_index.category_id = '42'
               INNER JOIN
           `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id
               AND price_index.website_id = '2'
               AND price_index.customer_group_id = 0
               LEFT JOIN
           `catalog_product_entity_decimal` AS `specialPrice` ON specialPrice.`entity_id` = `e`.entity_id
               AND `specialPrice`.`attribute_id` = 76
               LEFT JOIN
           `catalog_product_entity_datetime` AS `specialPriceFrom` ON specialPriceFrom.`entity_id` = `e`.entity_id
               AND `specialPriceFrom`.`attribute_id` = 77
               LEFT JOIN
           `catalog_product_entity_datetime` AS `specialPriceTo` ON specialPriceTo.`entity_id` = `e`.entity_id
               AND `specialPriceTo`.`attribute_id` = 78
               LEFT JOIN
           `catalogrule_product_price` AS `catalogRule` ON catalogRule.`product_id` = `e`.entity_id
      WHERE (e.status = 1)
        AND (
          (
                  specialPrice.value IS NOT NULL
                  AND (specialPriceFrom.value < NOW()
                  OR specialPriceFrom.value IS NULL
                      )
                  AND (
                          specialPriceTo.value > NOW()
                          OR specialPriceTo.value IS NULL
                      )
              )
          )
 
      UNION ALL
 
      SELECT e.entity_id
      FROM `catalog_product_flat_3` AS `e`
               INNER JOIN
           `catalog_category_product_index` AS `cat_index` ON cat_index.product_id = e.entity_id
               AND cat_index.store_id = 3
               AND cat_index.visibility IN (2, 4)
               AND cat_index.category_id = '42'
               INNER JOIN
           `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id
               AND price_index.website_id = '2'
               AND price_index.customer_group_id = 0
               LEFT JOIN
           `catalog_product_entity_decimal` AS `specialPrice` ON specialPrice.`entity_id` = `e`.entity_id
               AND `specialPrice`.`attribute_id` = 76
               LEFT JOIN
           `catalog_product_entity_datetime` AS `specialPriceFrom` ON specialPriceFrom.`entity_id` = `e`.entity_id
               AND `specialPriceFrom`.`attribute_id` = 77
               LEFT JOIN
           `catalog_product_entity_datetime` AS `specialPriceTo` ON specialPriceTo.`entity_id` = `e`.entity_id
               AND `specialPriceTo`.`attribute_id` = 78
               LEFT JOIN
           `catalogrule_product_price` AS `catalogRule` ON catalogRule.`product_id` = `e`.entity_id
      WHERE (e.status = 1)
        AND (
          (
                  `catalogRule`.website_id = 2
                  AND `catalogRule`.customer_group_id = 0
              )
          )
     ) tmp;