Databases Reference
In-Depth Information
For example, you might need many different index combinations to speed up various
types of queries. These conflicting requirements sometimes demand that you create a
cache table that contains only some of the columns from the main table. A useful tech-
nique is to use a different storage engine for the cache table. If the main table uses
InnoDB, for example, by using MyISAM for the cache table you'll gain a smaller index
footprint and the ability to do full-text search queries. Sometimes you might even want
to take the table completely out of MySQL and into a specialized system that can search
more efficiently, such as the Lucene or Sphinx search engines.
When using cache and summary tables, you have to decide whether to maintain their
data in real time or with periodic rebuilds. Which is better will depend on your appli-
cation, but a periodic rebuild not only can save resources but also can result in a more
efficient table that's not fragmented and has fully sorted indexes.
When you rebuild summary and cache tables, you'll often need their data to remain
available during the operation. You can achieve this by using a “shadow table,” which
is a table you build “behind” the real table. When you're done building it, you can swap
the tables with an atomic rename. For example, if you need to rebuild
my_summary
, you
can create
my_summary_new
, fill it with data, and swap it with the real table:
mysql>
DROP TABLE IF EXISTS my_summary_new, my_summary_old;
mysql>
CREATE TABLE my_summary_new LIKE my_summary;
-- populate my_summary_new as desired
mysql>
RENAME TABLE my_summary TO my_summary_old, my_summary_new TO my_summary;
If you rename the original
my_summary
table
my_summary_old
before assigning the name
my_summary
to the newly rebuilt table, as we've done here, you can keep the old version
until you're ready to overwrite it at the next rebuild. It's handy to have it for a quick
rollback if the new table has a problem.
Materialized Views
Many database management systems, such as Oracle or Microsoft SQL Server, offer a
feature called
materialized views
. These are views that are actually precomputed and
stored as tables on disk, and can be refreshed and updated through various strategies.
MySQL doesn't support this natively (we'll go into details about its support for views
in
Chapter 7
). However, you can implement materialized views yourself, using Justin
Swanhart's open source Flexviews tools (
http://code.google.com/p/flexviews/
)
. Flex-
views is more sophisticated than roll-your-own solutions and offers a lot of nice features
that make materialized views simpler to create and maintain. It consists of a few parts:
• A Change Data Capture (CDC) utility that reads the server's binary logs and ex-
tracts relevant changes to rows
• A set of stored procedures that help define and manage the view definitions
• Tools to apply the changes to the materialized data in the database
