We have been using the MB Relationships extension on the platform we have built and everything was going well, but on our last site with more data than I have handled so far we have encountered performance problems in the loading of the relationship fields.
What I have seen and please correct me if I am wrong, is that the selects on the relationship fields are builded with Post fields and in each of them query all posts of that post type and this is killing the database.
My thought is to set a pagination here and then get a lazy loading going so that when a person opens a select field and scrolls to the bottom of the list or types in a name into the search field, a query is then made to populate the next 50 or 100 posts or the proper search result. This will probably happen via Ajax.
Some of our posts have 700 relationships and more than 4,000 possible options within individual relationship fields.
Do you think my analysis is correct? And do you think that implement paging and lazy loading to solve it is a good approach?
This is very important for the site I am building, and time sensitive because the admin remains unusable until it is fixed. Thank you for your time.