Forum
Perch database question - slow perch_collection() call
I’m redesigning a fairly mature Runway site. Migrated the existing database and am redoing all presentational templates. Structure of the data (perch content, collections + those templates that define the data structure) will not change.
A perch_collection()
call for one of my collections took a huge amount of time (> 40 secs) on the new site/db. I tracked down the offending SQL, and it was a group by
query on the perch3_collection_index
table, which contained > 200k rows (this doesn’t seem like a huge amount to me for a mature site with reasonably complex templates).
So I emptied that table + re-saved my perch content and everything is super-fast now.
I’m not sure exactly what my questions are, but they probably include:
- why would the query run so much slower on my migrated db? Could there be some Perch indexing or caching in play on my existing site that isn’t there on my new site or migrated db.
- any downside to emptying the
perch3_collection_index
table, other than losing revision history for my collections? - is there some built-in way to clean up or optimise Perch dbs that I should be aware of?
Please post your Diagnostics Report and the relevant templates.
Hi Rachel,
It was a general question so I didn't see the need to include the diagnostics, but I probably should have done. Sorry. They are below.
Another question I have is: is there some config setting that can minimise the number of collection (content) revisions that are held in the db? Are/can the oldest revisions (be) removed intermittently by a scheduled task?
Diagnostics and template for the collection I am running
perch_collection()
on (sub-templates not included, they are not complex):Note this is after I have emptied the
perch3_collection_index
table and re-saved my content, hence the diagnostics report is not showing 200k rows.and the template:
Is this a template for your Products collection? If so, it looks like you have a circular relationship, which will cause index bloat. We've been working on some changes for the next release that should help with that.
Ah ok, thanks Drew, hadn't considered that might be a problem. I guess my questions still stand, but I'll write a custom fieldtype to remove the circular reference .
I'd wait for the next update and see if it helps - could save you some work.
Great, thanks.
I'm running into a similar problem with my perch3_collection_index getting very large and slow to update. I'm intrigued by the custom field "fix" - how exactly does that work? I'm certain it's the circular references in my related content.
In general, I never try to edit the database directly, but if clearing/cleaning the collection_index would help I might consider it.