We are transitioning over to a new forum platform. Please post new issues there. Existing threads will be dealt with here unless you choose to move them over. Visit the new forum

Forum

Thread tagged as: Problem, Runway

Perch2_collection_index table is pretty, pretty big

Hi there

I'm developing a site with Perch Runway.

I noticed that the SQL file was getting rather large – 46Mb. Upon further inspection I saw that the collection_index table has ballooned to 300k+ lines, give or take. Saving any items in collections took forever. I've only got 4 collections with 45 items in total. Some are related mind you.

I've resolved this on the dev site by emptying the table and re-saving all the collections again. It's now 6,986 lines and all works fine again.

  1. Is this a BAD thing to do? (I'm guessing, yes!)
  2. Is there a script I can run to delete orphaned lines in the database that would be less drastic?
  3. Have I unearthed a bug and, if so, what do I win? I have the offending SQL file if it helps.

Many thanks in advance,

Kind regards

Jon

Jonathan Elliman

Jonathan Elliman 27 points

  • 3 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

That doesn't sound right. Have you accidentally created any circular relationships?

Hi Drew

A project item can be related to services. A service item can be related to projects. So I guess, yes. Is this bad?

Drew McLellan

Drew McLellan 2638 points
Perch Support

Yes, that's bad, and that's what's filling up your database. You should only need the relationship in one direction.

OK, thanks Drew. I will rethink how it works.

Please can I make a request that the relationship page is updated with this warning for idiots like me?

Is emptying the index's rows going to be a problem in the future?

Drew McLellan

Drew McLellan 2638 points
Perch Support

No, it's not a problem. Your items just won't be indexed if you remove them from the index.

OK thanks Drew.

Last question, honest.

So if a user can relate a project to a type of service in a service collection, how do I go about listing the related services on the project page? Is that even possible?

Jon

Drew McLellan

Drew McLellan 2638 points
Perch Support

Without details I'll just have to guess, but something like:

perch_collection('Services', [
    'filter' => 'project.slug',
    'value' => perch_get('s'),
]);

Right - Thanks Drew. I'm thinking inside the content template. I will extrapolate everything out into the page and try again.

Many thanks again,

Jon

Drew McLellan

Drew McLellan 2638 points
Perch Support

If you need it in the template, do it in the page and then pass it into the template.

Try to keep things modular, as you'll get better performance that way.

Sir, yes sir.

All sorted now and working as previous. Thanks.

Jon