I’m reviewing some further DB optimizations we can do in Pods 2.0 to increase performance with less JOINs for relationships as well as more abilities long-term for how we use findRecords (think company.industry.name = “Wholesale” type of stuff).

Feel free to suggest any ideas you might have and we can discuss further.

So far, I’ve been thinking that beyond our simplification of the relationship table itself, perhaps storing relationships in the primary table for a content type would further increase performance. For example, if you have a single-select relationship, we could maybe store that ID in a field on the content type’s table and also in the relationship table (with the full relationship info). So in many cases, when you want to do something like company.id = {$company_id} where company.id triggers a single JOIN of the company content type’s table ON company.id = t._company versus what would normally require two tables being joined between the company content type’s table AND the relationship table.

Along with that idea, perhaps it could also be considered to store a list of IDs for multi-select relationships as 1|2|3|4 where we could use REGEXP to perform the JOIN without hitting the relationships table?

We could also add more indexes on the tables, for instance, we could add an index for the slug fields on each table to speed up lookups by slug. We could also add an index to other frequently used fields on core tables (beyond ID) to speed lookups by those fields as well. We could also add an advanced feature for Developers to choose additional indexes for fields which may merit them on complex sites so they have that kind of control readily available to them.

Just throwing out some ideas to facilitate the discussion as to what we could do to enhance performance on complex sites beyond just caching.

Any thoughts? Any optimizations you think we could put into Pods to help your site run faster?