Materialized Views in MySQL? (aka, Summary Tables / aka, Materialized Views / aka, Flexviews ) + more!!

-- Initially I was looking to not hit a product table with hundreds of thousands of products for randomly getting a few with every visit to the homepage, -- similar to how views in MSSQL work and also let me pull the data in the off hours for usage in reports and widgets for featured and new products as well. -- -- ... what I found is really a matter of your preference, but it does have my flare for implementation in a separate personal repository which is different -- than this one's audience. -- -- It's easier if you start out with a... CREATE TABLE theSmallerTable LIKE theBiggerFullTable; -- so you can just do this when you really needed to however you wanted to DELETE FROM theSmallerTable WHERE 1; -- droppin loads of data all over... thank you nick manning. INSERT INTO theSmallerTable SELECT * FROM theBiggerFullTable; -- just add whatever indexes or crazy fills you want in and around, but also for those brave enough, -- here's some triggers and other advanced playground stuff.. DELIMITER | CREATE TRIGGER trig_mviCity AFTER INSERT ON tCity FOR EACH ROW BEGIN INSERT INTO mvStateCity SELECT s.StateID, s.StateName, NEW.CityID, NEW.CityName FROM tState s WHERE s.StateID=NEW.StateIDFK; END; | CREATE TRIGGER trig_mvdCity AFTER DELETE ON tCity FOR EACH ROW BEGIN DELETE FROM mvStateCity WHERE CityID=OLD.CityID; END; | CREATE TRIGGER trig_mvuCity AFTER UPDATE ON tCity FOR EACH ROW BEGIN UPDATE mvStateCity SET CityName=NEW.CityName, StateID=NEW.StateIDFK WHERE CityID=NEW.CityID; END; | CREATE TRIGGER trig_mvdState AFTER DELETE ON tState FOR EACH ROW BEGIN DELETE FROM mvStateCity WHERE StateID=OLD.StateID; END; | DELIMITER ; -- above is self maintaining code, but you can schedule the triggers with garbage collectors, or events, or cron, or you get the idea. -- here's a stored procedure: DROP PROCEDURE refresh_mv_now; DELIMITER $$ CREATE PROCEDURE refresh_mv_now ( OUT rc INT ) BEGIN TRUNCATE TABLE sales_mv; INSERT INTO sales_mv SELECT product_name , SUM(product_price), SUM(product_amount) , AVG(product_price), AVG(product_amount) , COUNT(*) FROM sales GROUP BY product_name; SET rc = 0; END; $$ DELIMITER ; -- way more detail (good read!) from here: -- -- flexviews: or -- summary tables:
Views in MySQL are dynamic. What if you want to cache the info so you don't crash the live system with poorly written queries during the day? Too bad and you're SOL, Jack. Here's what MySQL says...

.. and above are your prayers answered, as mine were. Open Source just means you have to think harder with less hand holding.

Be the first to comment

You can use [html][/html], [css][/css], [php][/php] and more to embed the code. Urls are automatically hyperlinked. Line breaks and paragraphs are automatically generated.