-- 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: http://www.fromdual.com/mysql-materialized-views
--
-- flexviews: https://github.com/greenlion/swanhart-tools/tree/master/flexviews or http://greenlion.github.io/swanhart-tools/flexviews/manual.html
-- summary tables: http://mysql.rjweb.org/doc.php/summarytables
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...
http://dev.mysql.com/doc/refman/5.6/en/create-view.html
.. and above are your prayers answered, as mine were. Open Source just means you have to think harder with less hand holding.
http://dev.mysql.com/doc/refman/5.6/en/create-view.html
.. 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.