To add or not to add an index. Let the Mendix-platform decide on that.
Currently this is left to the developer. But that decision actually depends on runtime-characteristics. It weighs the cost of maintaining the index-table versus the gain in performance during read-operations.
The first is also dependent on the amount of times of writing the indexed objects.
The second is also dependent on the amount of times of reading the indexed objects.
These quantities can vary during the lifespan of an application. Most often performance-drops start happening when the number of objects of an entity increases and become annoying for the user. A good time to start maintaining an index. This is ideally a task of a database-manager who can monitor this and foresee the performance drop and take the pre-emptive counter-measure: create the index. But, in the absence of this management in Mendix applications, we just wait for users to start complaining, then do a little research and add an index to the entity. Never to look at it again, so if entity-usage changes, and the index is not the best option anymore, we never remove it.
Just always add an index, as the "Best practice recommender" tells us? Well, this also suggests creating indexes on entities with very little objects in them, and on entities that get written often and hardly ever get read. Generating needless database-growth and cost during write. Usually not a big problem, but not optimal. Therefor:
Let the Mendix-platform and/or the database take care of the indexes.
As Fabian states, some databases do this automatically, e.g., Oracle: https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/autonomous-auto-index.html
Generally a very cool idea. I would rather expect this kind of performance tuning on the database itself.
Hi Tim,
question: at which point would the (need for) indexes be evaluated? During startup? Or on-the-fly?
To be honest I feel there are many variables in the mix which could prevent successful (and predictable) results of the feature. I'd love to see either Mendix or the database engine companies try, as long as there is also an option to turn it off when needed ;)