Databases Reference
In-Depth Information
In contrast to typical methods of maintaining summary and cache tables, Flexviews
can recalculate the contents of the materialized view incrementally by extracting delta
changes to the source tables. This means it can update the view without needing to
query the source data. For example, if you create a summary table that counts groups
of rows, and you add a row to the source table, Flexviews simply increments the cor-
responding group by one. The same technique works for other aggregate functions,
such as
SUM()
and
AVG()
. It takes advantage of the fact that row-based binary logging
includes images of the rows before and after they are updated, so Flexviews can see not
only the new value of each row, but the delta from the previous version, without even
looking at the source table. Computing with deltas is much more efficient than reading
the data from the source table.
We don't have space for a full exploration of how to use Flexviews, but we can give an
overview. You start by writing a
SELECT
statement that expresses the data you want to
derive from your existing database. This can include joins and aggregations (
GROUP
BY
). There's a helper tool in Flexviews that transforms your SQL query into Flexviews
API calls. Then Flexviews does all the dirty work of watching changes to the database
and transforming them into updates to the tables that store your materialized view over
the original tables. Now your application can simply query the materialized view in-
stead of the tables from which it was derived.
Flexviews has good coverage of SQL, including tricky expressions that you might not
expect a tool to handle outside the server. That makes it useful for building views over
complex SQL expressions, so you can replace complex queries with simple, fast queries
against the materialized view.
Counter Tables
An application that keeps counts in a table can run into concurrency problems when
updating the counters. Such tables are very common in web applications. You can use
them to cache the number of friends a user has, the number of downloads of a file, and
so on. It's often a good idea to build a separate table for the counters, to keep it small
and fast. Using a separate table can help you avoid query cache invalidations and lets
you use some of the more advanced techniques we show in this section.
To keep things as simple as possible, suppose you have a counter table with a single
row that just counts hits on your website:
mysql>
CREATE TABLE hit_counter (
->
cnt int unsigned not null
->
) ENGINE=InnoDB;
Each hit on the website updates the counter:
mysql>
UPDATE hit_counter SET cnt = cnt + 1;
