Magento 1.x Enterprise Partial Indexing Diagnostics Toolset

magento1_enterprise_index_diagnostics.sql

-- When products are added/removed from a category this table stores all the relation data
select * from catalog_category_product where product_id = '24526';

-- The index that is used for presenting products in a category on the frontend
select * from catalog_category_product_index where product_id = '24526';

-- The change log table used for Magento EE partial index functionality
select * from catalog_category_product_index_cl where product_id = '24526' and version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'catalog_category_product_index_cl');



-- When products are added/removed from a category this table stores all the relation data
select * from catalog_category_product where product_id = '218';

-- The index that is used for presenting products in a category on the frontend
select * from catalog_category_product_index where product_id = '218';

-- The change log table used for Magento EE partial index functionality (only list records not yet processed)
select * from catalog_category_product_index_cl where product_id = '218' and version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'catalog_category_product_index_cl');



-- Monitoring the cron job to check for error messages, execution time, and most recent finished time
select 	UTC_TIMESTAMP() `current_timestamp`,
		case 
			when s.status = 'running' then TIMESTAMPDIFF(MINUTE,s.executed_at,UTC_TIMESTAMP())
			when s.finished_at is null and s.executed_at is null then null
			else concat(TIMESTAMPDIFF(MINUTE,s.executed_at,s.finished_at), ':', (TIMESTAMPDIFF(SECOND,s.executed_at,s.finished_at) - (TIMESTAMPDIFF(MINUTE,s.executed_at,s.finished_at) * 60)))
		end `min_sec_executing`,
		s.*
from cron_schedule s
where s.job_code in ('enterprise_refresh_index', 'enterprise_index_clean_changelog', 'smart_merchandiser_reindex');
order by executed_at desc;



delete from cron_schedule where job_code = 'enterprise_refresh_index';
delete from cron_schedule where job_code = 'enterprise_index_clean_changelog';
insert into cron_schedule (job_code, status, created_at, scheduled_at) values ('enterprise_index_clean_changelog', 'pending', UTC_TIMESTAMP(), DATE_ADD(UTC_TIMESTAMP(),INTERVAL 0 MINUTE));

-- useful to see what the current UTC time is when comparing to the cron_schedule
select UTC_TIMESTAMP();

-- check for existance of table catalog_product_entity_tmp_indexer
show tables like 'catalog_product_entity_tmp_indexer';

-- Check index status
select * from index_process order by started_at;

-- Check to see how many records are in the change long table
-- Stage 20035887	2016-02-09 08:22:14
select count(*), UTC_TIMESTAMP() from catalog_category_product_index_cl;

-- check to see what version_id each enterprise partial index is currently at.
-- magento will use this when trying to process partial index rebuilds rather than full reindexing of the tables
select * from enterprise_mview_metadata;

select 	'cataloginventory_stock_status_cl' tablename, 
		(select count(*) from cataloginventory_stock_status_cl) total_count,
		(
			select count(*)
			from cataloginventory_stock_status_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'cataloginventory_stock_status_cl')
		) unindexed_count
union all
select 	'enterprise_url_rewrite_redirect_cl' tablename, 
		(select count(*) from enterprise_url_rewrite_redirect_cl) total_count,
		(
			select count(*)
			from enterprise_url_rewrite_redirect_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'enterprise_url_rewrite_redirect_cl')
		) unindexed_count
union all
select 	'enterprise_url_rewrite_category_cl' tablename, 
		(select count(*) from enterprise_url_rewrite_category_cl) total_count,
		(
			select count(*)
			from enterprise_url_rewrite_category_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'enterprise_url_rewrite_category_cl')
		) unindexed_count
union all
select 	'enterprise_url_rewrite_product_cl' tablename, 
		(select count(*) from enterprise_url_rewrite_product_cl) total_count,
		(
			select count(*)
			from enterprise_url_rewrite_product_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'enterprise_url_rewrite_product_cl')
		) unindexed_count
union all
select 	'catalog_category_product_index_cl' tablename, 
		(select count(*) from catalog_category_product_index_cl) total_count,
		(
			select count(*)
			from catalog_category_product_index_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'catalog_category_product_index_cl')
		) unindexed_count
union all
select 	'catalog_category_product_cat_cl' tablename, 
		(select count(*) from catalog_category_product_cat_cl) total_count,
		(
			select count(*)
			from catalog_category_product_cat_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'catalog_category_product_cat_cl')
		) unindexed_count
union all
select 	'catalog_product_index_price_cl' tablename, 
		(select count(*) from catalog_product_index_price_cl) total_count,
		(
			select count(*)
			from catalog_product_index_price_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'catalog_product_index_price_cl')
		) unindexed_count
union all
select 	'catalog_category_flat_cl' tablename, 
		(select count(*) from catalog_category_flat_cl) total_count,
		(
			select count(*)
			from catalog_category_flat_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'catalog_category_flat_cl')
		) unindexed_count
union all
select 	'catalog_product_flat_cl' tablename, 
		(select count(*) from catalog_product_flat_cl) total_count,
		(
			select count(*)
			from catalog_product_flat_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'catalog_product_flat_cl')
		) unindexed_count
union all
select 	'catalogsearch_fulltext_cl' tablename, 
		(select count(*) from catalogsearch_fulltext_cl) total_count,
		(
			select count(*)
			from catalogsearch_fulltext_cl where version_id > (select version_id from enterprise_mview_metadata where changelog_name = 'catalogsearch_fulltext_cl')
		) unindexed_count
;