Using MariaDb FlexiViews

In the quest to filtering data I ended up looking for a tool that allows me to move a part of a query that needed too many unions in order to achieve simple things that where not thought trough from the beginning.

Task at hand:

• make a step in moving from a faulty database design to a new organized data model
• don’t affect current query performance
• reduce the effort in building the query from backend
• apply conditions like using a field in a joined table for deciding the type of join the query needs
• keep query maintainable

A database view comes usually with the disadvantage of not having indexes like a table does, it depends on the performance of the query builder and most of the time is intended at shadowing sensitive information from possible sql query vulnerabilities. So it does add a step in the right direction, might not affect query performance and reduces the effort of building the query from backend, but sorting or ordering might become an overhaul.

Looking at MariaDb FlexiViews I was wondering which of these points does that help improve and arrived at the following conclusions:

• FlexiViews are good are preparing data for large requests
• it has some limitations. But making a workaround might be an improvement in performance
• Flexiviews are maintainable from php and plugabble, which is a great feature
• it actually uses a table for caching data

Found a nice introduction by the author https://www.slideshare.net/MySQLGeek/flexviews-materialized-views-for-my-sql .

For anyone considering development using flexviews consider reading the code base.