Item14037: PageCache needs an index on the to_topic field.

Very long topic save times on foswiki.org was traced to very long cache update times.

• foswiki_cache_pages: 55,457 rows
• foswiki_cache_deps: 7,050,189 rows

The only index on the deps table is: KEY `foswiki_cache_deps_index` (`from_topic`,`to_topic`)

However on topic update, the deps table is joined with the pages table, =where to_topic='the saved topic'; And is referenced again, to delete all the deps rows for that to_topic.

Adding a simple index on to_topic reduced a simple count(*) for a unique to_topic from 16 seconds to sub-second. This will have similar impact on the join and delete operation. Index has been added to foswiki.org, but we need to create this index by default.

-- GeorgeClark - 03 Apr 2016

This index should be created by

• Foswiki::PageCache::DBI::_createPagesTable
• Foswiki::PageCache::DBI::_createDepsTable
• Foswiki::PageCache::DBI::MySQL::_createPagesTable
• Foswiki::PageCache::DBI::MySQL::_createDepsTable

Which patch did you apply to them?

-- MichaelDaum - 04 Apr 2016

Commenting is disabled while not logged in