UPSTREAM FIX

magento2-40463: Performance fix the analytics_collect_data cron job long run

Community fix magento2-40463 merged into magento/magento2 on 2026-04-01, released in 2.4.9; applies cleanly to 33 releases from 2.4.6 to 2.4.8-p5.

Pull request title: Performance fix the analytics_collect_data cron job long run
Pull request: magento/magento2#40463
Issues: #40590 pr-derived
Author: @AlexRapatij
Merged: 2026-04-01
Fixed in: 2.4.9
Reported on: —
Categories: Reports
Components: magento/module-analytics

Labels

Area: Analytics / Reporting
Component: Analytics
Priority: P2
Severity: —
Reported on (labels): —

Issue

Title and steps come from the upstream issue and pull request.

Description

For a fairly large database, the analytics_collect_data cron job takes too long to execute. For one real example project, it took last time ~36 hours.
According to the investigation, the reason for that is the validation of the report definition query. By adding a limit of 0, it turns into a final SQL-query without any limits at all. As a result, there is a loading of all tables data.
The solution is super simple - to add a limit equal to 1 to the validation query

Steps to reproduce

1. Add a temporary custom logging before calling query (line 55). Unfortunately, the bin/magento dev:query-log:enable doesn't log such queries.

// Example of logging
try {
    \Magento\Framework\App\ObjectManager::getInstance()->get(\Psr\Log\LoggerInterface::class)
        ->info('Report validation query: ' . $query->getSelect()->__toString());
    $connection->query($query->getSelect());
} catch (\Zend_Db_Statement_Exception $e) {
    return [$name, $e->getMessage()];
}

2. Execute the analytics_collect_data cron job

php n98-magerun2.phar sys:cron:run analytics_collect_data

3. Check the logs

grep 'Report validation query' var/log/system.log

### Examples
#### Before change
There is queries, before the change was applied

SELECT `catalog_category_entity_text`.`value` AS `content` FROM `catalog_category_entity_text`
SELECT `catalog_product_entity_text`.`value` AS `content`, `eav_attribute`.`attribute_code` FROM `catalog_product_entity_text`
SELECT `cms_page`.`content` FROM `cms_page`
SELECT `cms_block`.`content` FROM `cms_block`
SELECT `setup_module`.`module` AS `module_name`, `setup_module`.`schema_version`, `setup_module`.`data_version` FROM `setup_module` 
SELECT `store`.`store_id`, `store`.`code`, `store`.`group_id`, `store`.`name`, `store`.`is_active` FROM `store` 
SELECT `store_website`.`website_id`, `store_website`.`code`, `store_website`.`name`, `store_website`.`default_group_id`, `store_website`.`is_default` FROM `store_website` 
SELECT `store_group`.`group_id`, `store_group`.`website_id`, `store_group`.`name`, `store_group`.`default_store_id` FROM `store_group` 
SELECT `catalog_product_entity`.`entity_id`, `catalog_product_entity`.`sku` FROM `catalog_product_entity` WHERE (catalog_product_entity.created_in <= '1748965680') AND (catalog_product_entity.updated_in > '1748965680') 
SELECT `magento_banner_content`.`banner_content` AS `content` FROM `magento_banner_content`
SELECT `quote`.`entity_id`, `quote`.`customer_id`, `quote`.`store_id`, `quote`.`created_at`, `quote`.`converted_at`, `quote`.`is_active`, `quote`.`items_count`, `quote`.`items_qty`, `quote`.`orig_order_id` FROM `quote` 
SELECT `review`.`review_id`, `review`.`created_at`, `review`.`entity_pk_value` FROM `review` 
SELECT `rating_option_vote_aggregated`.`primary_id`, `rating_option_vote_aggregated`.`entity_pk_value`, `rating_option_vote_aggregated`.`store_id`, `rating_option_vote_aggregated`.`rating_id`, `rating_option_vote_aggregated`.`percent_approved` FROM `rating_option_vote_aggregated` 
SELECT `sales_order`.`entity_id`, `sales_order`.`created_at`, `sales_order`.`customer_id`, `sales_order`.`status`, `sales_order`.`base_grand_total`, `sales_order`.`base_tax_amount`, `sales_order`.`base_shipping_amount`, SHA1(`sales_order`.`coupon_code`) AS `coupon_code`, `sales_order`.`store_id`, `sales_order`.`store_name`, `sales_order`.`base_discount_amount`, `sales_order`.`base_subtotal`, `sales_order`.`base_total_refunded`, `sales_order`.`shipping_method`, `sales_order`.`shipping_address_id`, SHA1(`sales_order`.`customer_email`) AS `customer_email`, `sales_order`.`base_total_online_refunded`, `sales_order`.`base_total_offline_refunded`, `sales_order`.`base_currency_code`, `sales_order`.`billing_address_id` FROM `sales_order` 
SELECT `sales_order_item`.`item_id`, `sales_order_item`.`created_at`, `sales_order_item`.`name`, `sales_order_item`.`base_price`, `sales_order_item`.`qty_ordered`, `sales_order_item`.`order_id`, `sales_order_item`.`sku`, `sales_order_item`.`product_id`, `sales_order_item`.`store_id` FROM `sales_order_item` 
SELECT `sales_order_address`.`entity_id`, `sales_order_address`.`customer_id`, `sales_order_address`.`city`, `sales_order_address`.`region`, `sales_order_address`.`country_id` FROM `sales_order_address` 
SELECT `customer_entity`.`entity_id`, `customer_entity`.`created_at`, SHA1(`customer_entity`.`email`) AS `email`, `customer_entity`.`store_id` FROM `customer_entity` 
SELECT `wishlist`.`wishlist_id`, `wishlist`.`customer_id` FROM `wishlist` 
SELECT `wishlist_item`.`wishlist_item_id`, `wishlist_item`.`added_at`, `wishlist_item`.`qty`, `wishlist_item`.`store_id`, `wishlist_item`.`wishlist_id`, `wishlist_item`.`product_id` FROM `wishlist_item`

#### After
After the limitation was added

SELECT `catalog_category_entity_text`.`value` AS `content` FROM `catalog_category_entity_text`
SELECT `catalog_product_entity_text`.`value` AS `content`, `eav_attribute`.`attribute_code` FROM `catalog_product_entity_text`
SELECT `cms_page`.`content` FROM `cms_page`
SELECT `cms_block`.`content` FROM `cms_block`
SELECT `setup_module`.`module` AS `module_name`, `setup_module`.`schema_version`, `setup_module`.`data_version` FROM `setup_module` LIMIT 1 
SELECT `store`.`store_id`, `store`.`code`, `store`.`group_id`, `store`.`name`, `store`.`is_active` FROM `store` LIMIT 1 
SELECT `store_website`.`website_id`, `store_website`.`code`, `store_website`.`name`, `store_website`.`default_group_id`, `store_website`.`is_default` FROM `store_website` LIMIT 1 
SELECT `store_group`.`group_id`, `store_group`.`website_id`, `store_group`.`name`, `store_group`.`default_store_id` FROM `store_group` LIMIT 1 
SELECT `catalog_product_entity`.`entity_id`, `catalog_product_entity`.`sku` FROM `catalog_product_entity` WHERE (catalog_product_entity.created_in <= '1748965680') AND (catalog_product_entity.updated_in > '1748965680') LIMIT 1 
SELECT `magento_banner_content`.`banner_content` AS `content` FROM `magento_banner_content`
SELECT `quote`.`entity_id`, `quote`.`customer_id`, `quote`.`store_id`, `quote`.`created_at`, `quote`.`converted_at`, `quote`.`is_active`, `quote`.`items_count`, `quote`.`items_qty`, `quote`.`orig_order_id` FROM `quote` LIMIT 1 
SELECT `review`.`review_id`, `review`.`created_at`, `review`.`entity_pk_value` FROM `review` LIMIT 1 
SELECT `rating_option_vote_aggregated`.`primary_id`, `rating_option_vote_aggregated`.`entity_pk_value`, `rating_option_vote_aggregated`.`store_id`, `rating_option_vote_aggregated`.`rating_id`, `rating_option_vote_aggregated`.`percent_approved` FROM `rating_option_vote_aggregated` LIMIT 1 
SELECT `sales_order`.`entity_id`, `sales_order`.`created_at`, `sales_order`.`customer_id`, `sales_order`.`status`, `sales_order`.`base_grand_total`, `sales_order`.`base_tax_amount`, `sales_order`.`base_shipping_amount`, SHA1(`sales_order`.`coupon_code`) AS `coupon_code`, `sales_order`.`store_id`, `sales_order`.`store_name`, `sales_order`.`base_discount_amount`, `sales_order`.`base_subtotal`, `sales_order`.`base_total_refunded`, `sales_order`.`shipping_method`, `sales_order`.`shipping_address_id`, SHA1(`sales_order`.`customer_email`) AS `customer_email`, `sales_order`.`base_total_online_refunded`, `sales_order`.`base_total_offline_refunded`, `sales_order`.`base_currency_code`, `sales_order`.`billing_address_id` FROM `sales_order` LIMIT 1 
SELECT `sales_order_item`.`item_id`, `sales_order_item`.`created_at`, `sales_order_item`.`name`, `sales_order_item`.`base_price`, `sales_order_item`.`qty_ordered`, `sales_order_item`.`order_id`, `sales_order_item`.`sku`, `sales_order_item`.`product_id`, `sales_order_item`.`store_id` FROM `sales_order_item` LIMIT 1 
SELECT `sales_order_address`.`entity_id`, `sales_order_address`.`customer_id`, `sales_order_address`.`city`, `sales_order_address`.`region`, `sales_order_address`.`country_id` FROM `sales_order_address` LIMIT 1 
SELECT `customer_entity`.`entity_id`, `customer_entity`.`created_at`, SHA1(`customer_entity`.`email`) AS `email`, `customer_entity`.`store_id` FROM `customer_entity` LIMIT 1 
SELECT `wishlist`.`wishlist_id`, `wishlist`.`customer_id` FROM `wishlist` LIMIT 1 
SELECT `wishlist_item`.`wishlist_item_id`, `wishlist_item`.`added_at`, `wishlist_item`.`qty`, `wishlist_item`.`store_id`, `wishlist_item`.`wishlist_id`, `wishlist_item`.`product_id` FROM `wishlist_item` LIMIT 1

Taken from the upstream pull request.

Error signatures

} catch (\Zend_Db_Statement_Exception $e) {

Code match per tag

Each tag was checked with git apply --check against that tag's files. A clean match means the change applies; it is not a test result. Tags that already contain the fix are marked.

Line	Code match per tag	Tests
2.4.6	2.4.6 clean 2.4.6-p1 clean 2.4.6-p2 clean 2.4.6-p3 clean 2.4.6-p4 clean 2.4.6-p5 clean 2.4.6-p6 clean 2.4.6-p7 clean 2.4.6-p8 clean 2.4.6-p9 clean 2.4.6-p10 clean 2.4.6-p11 clean 2.4.6-p12 clean 2.4.6-p13 clean 2.4.6-p14 clean 2.4.6-p15 clean	2.4.6: no test data 2.4.6-p1: no test data 2.4.6-p2: no test data 2.4.6-p3: no test data 2.4.6-p4: no test data 2.4.6-p5: no test data 2.4.6-p6: no test data 2.4.6-p7: no test data 2.4.6-p8: no test data 2.4.6-p9: no test data 2.4.6-p10: no test data 2.4.6-p11: no test data 2.4.6-p12: no test data 2.4.6-p13: no test data 2.4.6-p14: no test data 2.4.6-p15: no test data
2.4.7	2.4.7 clean 2.4.7-p1 clean 2.4.7-p2 clean 2.4.7-p3 clean 2.4.7-p4 clean 2.4.7-p5 clean 2.4.7-p6 clean 2.4.7-p7 clean 2.4.7-p8 clean 2.4.7-p9 clean 2.4.7-p10 clean	2.4.7: no test data 2.4.7-p1: no test data 2.4.7-p2: no test data 2.4.7-p3: no test data 2.4.7-p4: no test data 2.4.7-p5: no test data 2.4.7-p6: no test data 2.4.7-p7: no test data 2.4.7-p8: no test data 2.4.7-p9: no test data 2.4.7-p10: test files do not apply to this releaseunit: could not run before, could not run after
2.4.8	2.4.8 clean 2.4.8-p1 clean 2.4.8-p2 clean 2.4.8-p3 clean 2.4.8-p4 clean 2.4.8-p5 clean	2.4.8: no test data 2.4.8-p1: no test data 2.4.8-p2: no test data 2.4.8-p3: no test data 2.4.8-p4: no test data 2.4.8-p5: test files do not apply to this releaseunit: could not run before, could not run after
2.4.9	2.4.9 conflictcontains the fix	2.4.9: no test data

Triage

Model @cf/cloudflare/clef. Probability this is a bug fix: 81.8%. Probability it is security relevant: 0.7%.

Show the model's answers and probabilities

Question	Answer	Probabilities	Confidence
Change kind	bugfix	bugfix 90.7%, refactor 5.2%, tests_only 1.4%, feature 1.4%, dependency 0.7%, docs_only 0.6%	79.1%
Area	other	other 43.3%, framework 33.1%, admin 10.3%	21.3%
Reported version	unspecified	unspecified 27.6%, 2.4.8 4.8%, 2.4.8-p1 3.9%	7.7%
Scope	0.40 of 2	0 65.0%, 1 30.4%, 2 4.7%	27.5%
Risk	0.19 of 2	0 85.8%, 1 9.1%, 2 5.1%	62.1%
Worth backporting	1.66 of 2	2 73.4%, 1 19.7%, 0 6.9%	37.3%

Download

For cweagans/composer-patches, choose a version below and download the bundle. Copy its magento2-40463/ folder into patches/composer/, merge composer.patches.json into composer.json, then run composer install. Test files are always removed; paths are relative to each package root, using the default -p1 level.

Bundle README (what the ZIP ships)

# magento2-40463

Community fix merged upstream into magento/magento2, adapted by magento.watch.
This is not a patch published by Adobe.

Pull request: https://github.com/magento/magento2/pull/40463
Issue: https://github.com/magento/magento2/issues/40590
Author: @AlexRapatij
Source commit: cc8375f1b63faff46e2921d2baa5fb31637ea115
Modifications: test files and documentation removed, paths rewritten relative to each Composer package.
Licence: OSL-3.0 / AFL-3.0, as the original Magento Open Source code.
Maintainer: Łukasz Bajsarowicz (@lbajsarowicz)

Licence: Magento Open Source code under OSL-3.0 and AFL-3.0. The bundle carries the original author, source commit and the list of modifications.