My list with 5 joins is very slow

[Borox]

Hi,

I have a table with 5 joins:

The main table has about 350 records, the joined tables have between 100 and 700 records.

When I display the list it's very very slow ( more than 1mn!)
Of course part of these performances are due to my server ( dedicate server, 4 CPU, 8Go RAM), but the number of records in the table is not that high.
I dropped the joined table to see what happen and the performance increased, so the problem seems to be the joins.
I optimized and repaired the tables with phpMyAdmin, but it had no effect.
Even the use of the pagination is very slow ( 20s between pages)

Any idea to improve the performance?

Thanks

 

[troester]

Assuming the joins are repeatable they will create 350 x 100 x 700 x ... records, you can imagine.
Then Fabrik has to "merge rows" (if this is selected).
With multiple joins (or multiple repeat groups) hitting also php memory limits etc is not so unlikely.

1. Check if there are indexes on all your foreign keys (To columns)

2. Usually you won't show all these elements in list view. In this case I create a list copy, remove all joins not needed for the list view and set custom add/edit/view links to the "full" form + a redirect plugin back to the "reduced" list.

 

[Borox]

Assuming the joins are repeatable they will create 350 x 100 x 700 x ... records, you can imagine.
Then Fabrik has to "merge rows" (if this is selected).
With multiple joins (or multiple repeat groups) hitting also php memory limits etc is not so unlikely.

1. Check if there are indexes on all your foreign keys (To columns)

2. Usually you won't show all these elements in list view. In this case I create a list copy, remove all joins not needed for the list view and set custom add/edit/view links to the "full" form + a redirect plugin back to the "reduced" list.

I understand that fabrik has a lot to do to compute the records, I just meant that 100 even 700 records in a table it's not a lot.
1. Yep I have the indexes needed.
2. Nice tip, you're right! that's a good idea.

The advise to remember: not use too many joins?

I consider #2 and I'll let know the result in the forum.


Thanks @troester !

 

[troester]

Yup, even some 1000 records in a table are not a lot. But joins are becoming exponential.

I have an application with "only" 4 repeat groups (which are technically joins) and was not only in trouble with the list view but also running into limit issues with the form. No problem if somebody is adding 2,2,2,100 entries. But if someone is trying 10,10,10,10...

 

[Borox]

I've done a copy of my table ( contains 373 records) with just one join ( 748 records) and I used some customized links to the table wich has all the joins. The time to display the list has decreased by 40 times! (was 85s now 2s)
Thanks @troester for your tip, it works great!