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

  • 5 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

5 years ago

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

Jonathan Elliman 27 points

5 years ago

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

5 years ago

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

Jonathan Elliman 27 points

5 years ago

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

5 years ago

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

Jonathan Elliman 27 points

5 years ago

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

5 years ago

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

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

Jonathan Elliman 27 points

5 years ago

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

5 years ago

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.
Jonathan Elliman

Jonathan Elliman 27 points

5 years ago

Sir, yes sir.

All sorted now and working as previous. Thanks.

Jon