Forked from mttjohnson/magento1_enterprise_index_diagnostics.sql
Created
October 6, 2016 17:26
-
-
Save gamort/2f581615ebdf447ba91a4e0badc058e5 to your computer and use it in GitHub Desktop.
Magento 1.x Enterprise Partial Indexing Diagnostics Toolset
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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'); | |
-- The cron job that gets executed on each cron execution - check for errors | |
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 TIMESTAMPDIFF(MINUTE,s.executed_at,s.finished_at) | |
end `minutes_executing`, | |
s.* | |
from cron_schedule s | |
where s.job_code in ('enterprise_refresh_index', 'enterprise_index_clean_changelog', 'smart_merchandiser_reindex'); | |
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 | |
; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment