My SQL Dump

MySQL musings by a self professed MySQL Geek

Previous Entry Share Next Entry
Flexviews - minor improvements, further plans
swanhart
I've made a few fixes to the SVN version of Flexviews:
flexviews.enable() would return an error when trying to create an incremental refresh materialized view without an AVG() aggregate expression in the select clause.

Performance of DML is now improved substantially. Flexviews is designed to work in an environment with serialized DML (such as a MySQL slave). I removed extra serialization enforcement overhead. Previously two tables were involved in coordinating the 'uow_id' generation to ensure serialization in the logs. Now only one table is utilized. I need to add a procedure to auto-cleanup this new table.


There and there are a lot more to make. On my list of things to do:
1) code cleanup
flexviews went through several iterations and there are still 'mview' references all over the
place

some of the code is encased in comments because it was extracted by mysqldump

there is some extra code here and there that isn't used anymore. There might still be
some 'special refresh' spaghetti out there too.

it needs tests too. mysqltest should suffice

2) performance
the flexviews triggers currently add quite a bit of overhead to DML statements. I'd like to
add a configuration option to mysqld to turn off 'optimizing' before images in row based logs
so that I can capture changes from a binlog instead of with triggers

3) usability
currently, flexviews is very much garbage-in, garbage-out. The stored procedures need to do
some better sanity checking of input, and the procedures need to run in a stricter SQL_MODE

I really would like to somehow integrate the MySQL parser (maybe with a UDF) into flexviews
to remove the stored procedure API around creating views. I'd like to be able to parse a
SELECT statement into a view. This is mostly a pipedream, but I'd love to figure out a
way to make it work.

There is no real documentation outside of the example, and that doesn't include a
COMPLETE refresh view. I need better documentation.

I am going to follow up this email with a performance comparison between COMPLETE and INCREMENTAL refresh views, from both DML and refresh time standpoints.
Tags:

You are viewing swanhart