Materialized Views and MySQL

[IHateMyJob2004]

To my knowledge materialized views do not exist in MySQL.

I need this beacause I am currently doing a JOIN in a querry of mine that takes about 5 seconds. I join about 5 tables.

So, is the only alternative to crate a table and create an update script and run the update script periodically?

 

[Crusty]

Your update script could run on a trigger as well so you could make the updates more automated like a normal view would.

 

[KIAman]

Haha, I love your handle. Anyways, a materialized view is essentially a cached normal table so your only method without it is to create a table and update it.

Now to updating, you have several options. Triggers are good if you make sure to handle performance implications. If this subquery takes 5 seconds to run, you just got to make sure whatever is causing the trigger does not choke if it takes 5 extra seconds to complete. Also, if this is a heavy transactional database, you don't want this table to update all the time for performance reasons.

How realtime do you need this data? Can the data be an hour/day old? If so, setting up a job that does the update once per hour or day might be the better approach.

 

[IHateMyJob2004]

Tables are infrequently changes. Some once a day, others once a week. I'd have to update the "materialized table" daily to be as current as possible. I could just schedual the update to occur after updates to the parent tables have completed.

It is read frequently. Not sure of stats but it is low write, heavy read database.

Thanks for all the tips!

 

[Seero]

Tables are infrequently changes. Some once a day, others once a week. I'd have to update the "materialized table" daily to be as current as possible. I could just schedual the update to occur after updates to the parent tables have completed.

It is read frequently. Not sure of stats but it is low write, heavy read database.

Thanks for all the tips!

First I don't think you need to use Materialized view for that purpose. You need to fix the index and join condition of your query.

Second, maintaining triggers for materialized view can be a nightmare. Did it wrong and you have a materialized view that shows incorrect data.