Hacker News new | ask | show | jobs
by WesleyJohnson 1423 days ago
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.
2 comments
faxmeyourcode 1423 days 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.
link
johnthescott 1423 days 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.

link
WesleyJohnson 1423 days ago
I've been reading up on this as a potential solution; I may have to finally take it seriously and try it out.
link
johnthescott 1423 days 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-value...

link
johnthescott 1423 days 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

link