Hello Mendix Community, We have a Mendix solution deployed where Entity A has around 100+ attributes and 20+ associations as the owner. The number of records is currently 2,000,000, and it is expected to grow significantly over the coming years. We are brainstorming the idea of using a clustering index-based approach, where the records would be physically indexed based on whether the attribute Archived is True or False. We have access to the database and could implement this concept at the DB level. Could anyone with experience implementing this concept share their insights? This approach could potentially eliminate the need to create a separate Entity B to store archived records. With the clustering index solution, queries would fetch only the records where Archived is False directly from the clustered index. Below is the query that comes to mind for the clustering index solution. Please provide any insights if you have experience with this topic: How would the clustering index be utilized in this case, given that Mendix doesn’t support this activity out of the box? Would it require a Java action? Should we call the Java action at the startup microflow or before each retrieve activity in a microflow to make the clustering index work? How would this work when records are being fetched using widgets (e.g., a data grid)?
asked
Sandip Zade
1 answers
0
We also use such a boolean in XPaths to show 'active' records. But I think you only would need a clustered index if active records are all over your database. In our case active records are always newly creeated records and thus they already would be near each other. But your use case might be different.
And indeed Java actions would be needed if you want to manually do this. And you might want to create a support ticket to Mendix on how to do this in a safe way. My gut feeling would be that to do this only on a restart of the modeler in an after startup action. But may be Mendix support has that answer for you or will redirect you to Mendix services (but those are paid services).