CODEWITHSUNDEEP
mysql
Naveen Kumar
Naveen Kumar

Reputation: 632

Materialized view in mysql

How to create a materialized view in mysql ? I am not able to create a materialized view as we do in MS SQL Server.

Could anyone let me know the ways to create it in mysql.

Upvotes: 10

Views: 22398

Answers (3)

Gidon Wise
Gidon Wise

Reputation: 1916

www.LeapDB.com provides MySQL materialized views. straight forward with the proper syntax. It's an add on to MySQL.

Upvotes: 0

coding-dude.com
coding-dude.com

Reputation: 808

Here's what I've had success with so far:

  1. Using triggers - you can set triggers on the source tables on which you build the view. This minimizes the resource usage as the refresh is only done when needed. Also, data in the materialized view is realtime-ish
  2. Using cron jobs with stored procedures or SQL scripts - refresh is done on a regular basis. You have more control as to when resources are used. Obviously you data is only as fresh as the refresh-rate allows.
  3. Using MySQL scheduled events - similar to 2, but runs inside the database
  4. Flexviews - using FlexDC mentioned by Justin. The closest thing to real materialized views

I've been collecting and analyzing these methods, their pros and cons in my article Creating MySQL materialized views

looking forwards for feedback or proposals for other methods for creating materialized views in MySQL

Upvotes: 11

symcbean
symcbean

Reputation: 48357

You can create a non-updateable dynamic view - and if you've got (indexed) timestamps in the underlying table(s) you can add in a snapshot, something like:

CREATE VIEW almost_materialzd
AS
SELECT snp.*
FROM snapshot snp
WHERE s.id NOT IN (SELECT id
  FROM source_data sd
  INNER JOIN ref_data rd
  ON rd.value='snapshot of source_data'
  AND sd.update_timestamp>rd.timetamp)
UNION
SELECT *
FROM source_data sd2
INNER JOIN ref_data rd2
ON rd2.value='snapshot of source_data'
AND sd2.update_timestamp>rd2.timetamp);

But a better solution is to add a trigger (or triggers) to the the underlying table(s) to re-populate the relevant rows in a table representing the materialized view when the underlying tables are changed.

Upvotes: 7

Related Questions