3 ms·

One of the many reasons I wish we hadn't chosen MySQL. Our internal dashboard has loads of "reports" that are basically tabular displays of queried data with op

One of the many reasons I wish we hadn't chosen MySQL. Our internal dashboard has loads of "reports" that are basically tabular displays of queried data with optional filtering and sorting on every column, plus pagination. It's near impossible to make any of them performant due to not being able to optimize for a specific use case; because of the myriad of combinations of filters and sorts different users might apply. We've tried to explain this many times over, even bringing in a DBA consultant who said the same thing, but we're still met with "why is this so slow?" on a regular basis. Materialized views wouldn't be a silver bullet, but it would certainly help by allowing us to "cache" all of the joins and pre-processing into indexable views.

faxmeyourcode 4y ago
You should schedule a "create or replace table" statement every N hours/days/etc. It will be effectively just the same as a scheduled materialized view refresh.
- WesleyJohnson 4y ago
  I've been reading up on this as a potential solution; I may have to finally take it seriously and try it out.
  - deleted 4y ago
    [deleted]
  - johnthescott 4y ago
    the "t" at the end of "fact" implies past tense. if a materialized view is a statement of fact then you are safe to deploy in a relational schema, i claim. think about it. https://www.protechtraining.com/blog/post/the-value-of-values-with-rich-hickey-501 https://www.protechtraining.com/blog/post/the-value-of-value...
- johnthescott 4y ago
  seriously, does google "query" all the documents that you might be interested in? no, i argue google makes mat view(s) of your interests by querying the response database and specifically NOT the actual documents. remember, google only needs to be "kinda" CORRECT, unlike a relational db. try 'plaining that to management.
johnthescott 4y ago
is caching the correct analogy? the stars you see died eons ago. special relativity is your friend. we witness "immutability" casually. nothing in the relational model that i am aware of says that a materialized view cannot be a simple immutable table of "facts". same input same output. unambiguous. simply use the dynamic queries to summarize the "facts" in, typically, a small mat view tables and your gui will never lie. https://www.youtube.com/watch?v=-6BsiVyC1kM https://www.youtube.com/watch?v=-6BsiVyC1kM

faxmeyourcode 4y ago

You should schedule a "create or replace table" statement every N hours/days/etc. It will be effectively just the same as a scheduled materialized view refresh.

WesleyJohnson 4y ago
I've been reading up on this as a potential solution; I may have to finally take it seriously and try it out.
- deleted 4y ago
  [deleted]
- johnthescott 4y ago
  the "t" at the end of "fact" implies past tense. if a materialized view is a statement of fact then you are safe to deploy in a relational schema, i claim. think about it. https://www.protechtraining.com/blog/post/the-value-of-values-with-rich-hickey-501 https://www.protechtraining.com/blog/post/the-value-of-value...
johnthescott 4y ago
seriously, does google "query" all the documents that you might be interested in? no, i argue google makes mat view(s) of your interests by querying the response database and specifically NOT the actual documents. remember, google only needs to be "kinda" CORRECT, unlike a relational db. try 'plaining that to management.

johnthescott 4y ago

is caching the correct analogy? the stars you see died eons ago. special relativity is your friend. we witness "immutability" casually. nothing in the relational model that i am aware of says that a materialized view cannot be a simple immutable table of "facts". same input same output. unambiguous. simply use the dynamic queries to summarize the "facts" in, typically, a small mat view tables and your gui will never lie. https://www.youtube.com/watch?v=-6BsiVyC1kM https://www.youtube.com/watch?v=-6BsiVyC1kM