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.
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_indextable, 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?