MySQL materialized view

Resolved akt01
(@akt01)

4 years ago

Hi,

I have a SQL query that is quite resource intensive to run in real-time especially on a larger table, I’m trying to create a materialized view that I can run every X hours regularly. I understand that MySQL doesn’t support materialized view, I’m thinking to write a stored procedure that create a static table (the “materialized view”), and call this by cron to run periodically to refresh the “materialized view”.

The built-in wordpress database connector/handler like wpdb doesn’t support multiple statements/procedures. It feels like this is something that could be implemented with WP Data Access.

I’m wondering if anyone can help give any advice on this, or any alternative suggestions on potentially better ways to implement the “materialized view” are welcome.

Thank you!

Viewing 7 replies - 1 through 7 (of 7 total)

Plugin Author Passionate Programmer Peter
(@peterschulznl)

4 years ago

Hi @akt01,

My apologies for my extremely late reply! During my stay in Aruba I was not able to recharge my laptop battery which was limiting my internet access. I’ll try to pick up open issues asap now.

The Query Builder supports multiple SQL commands. This is documented here:
https://wpdataaccess.com/docs/documentation/query-builder/writing-queries/#multiple-sql-commands

But I’m afraid this will not completely satisfy your needs. I think you need to automatically run the SQL code in regular intervals. That option is planned but not yet available. Unless you don’t mind to run your SQL manually….?

Does this help?

Best regards,
Peter

charles godwin
(@charlesgodwin)

4 years ago

MySQL also supports an EVENT type which can run on a schedule. It can be multiple SQL statements.

https://dev.mysql.com/doc/refman/8.0/en/event-scheduler.html

Plugin Author Passionate Programmer Peter
(@peterschulznl)

4 years ago

I wasn’t aware of that MySQL feature Charles. Might make implementing my scheduler much easier… 😂

Thanks,
Peter

charles godwin
(@charlesgodwin)

4 years ago

It’s also supported by MariaDB https://mariadb.com/kb/en/events/

I think it will only run SQL statements.

Thread Starter akt01
(@akt01)

4 years ago

Hi @peterschulznl,

No prob! Thank you for your reply!

Is it possible to use WP Data Access to store a SQL query (preferably containing multiple statements), and call it via a wordpress hook? The hook can be called by cron, so WP Data Access does not have to handle the auto-scheduling.

Thanks!
Plugin Author Passionate Programmer Peter
(@peterschulznl)

4 years ago
Hi guys,

@charlesgodwin You just saved me a loooot of work… 😂 THX

@akt01 You can use multiple SQL statements in an event. Please visit this page:
https://dev.mysql.com/doc/refman/8.0/en/create-event.html

And search for: more complex compound statements

I tested this with the Query Builder and it works! Here is just some stupid code for testing:
```
CREATE EVENT e ON SCHEDULE EVERY 60 SECOND DO
BEGIN
	DECLARE v INTEGER;
	SET v = 0;
	truncate table aaa;
	WHILE v < 5 DO
	  INSERT INTO aaa VALUES (v);
	  SET v = v + 1;
	END WHILE;
END
/
```
Make sure you use the semi colon to separate your SQL statements and add a slash at the end of your code. I will add a page to the documentation so other users can use it as well.

Let me know if you need further assistance @akt01.

@charlesgodwin How can I sent you a beer? 😂

Best regards,
Peter
Thread Starter akt01
(@akt01)

4 years ago

Very interesting. I haven’t used EVENT before, it looks like MySQL has its own scheduler that can run scheduled jobs. And it’s nice that this can be setup via WP Data Access on WP systems. I’ll give it a try.

Thanks much @peterschulznl and @charlesgodwin, this is very helpful!

Viewing 7 replies - 1 through 7 (of 7 total)

The topic ‘MySQL materialized view’ is closed to new replies.