Advanced MySQL Features - High Performance MySQL - page 276

Because merge tables don't hide the underlying MyISAM tables, they offer some fea-
tures that partitions don't as of MySQL 5.5:
• A MyISAM table can be a member of many merge tables.
• You can copy underlying tables between servers by copying the .frm, .MYI ,
and .MYD files.
• You can add more tables to a merge collection easily; just alter the merge definition.
• You can create temporary merge tables that include only the data you want, such
as data from a specific time period, which you can't do with partitions.
• You can remove a table from the merge if you want to back it up, restore it, alter
it, repair it, or perform other operations on it. You can then add it back when you're
done.
• You can use myisampack to compress some or all of the underlying tables.
In contrast, a partitioned table's partitions are hidden by the MySQL server and are
accessible only through the partitioned table.
Views
Views were added in MySQL 5.0. A view is a virtual table that doesn't store any data
itself. Instead, the data “in” the table is derived from a SQL query that MySQL runs
when you access the view. MySQL treats a view exactly like a table for many purposes,
and views and tables share the same namespace in MySQL; however, MySQL doesn't
treat them identically. For example, you can't have triggers on views, and you can't
drop a view with the DROP TABLE command.
This topic does not explain how to create or use views; you can read the MySQL manual
for that. We'll focus on how views are implemented and how they interact with the
query optimizer, so you can understand how to get good performance from them. We
use the world sample database to demonstrate how views work:
mysql> CREATE VIEW Oceania AS
-> SELECT * FROM Country WHERE Continent = 'Oceania'
-> WITH CHECK OPTION;
The easiest way for the server to implement a view is to execute its SELECT statement
and place the result into a temporary table. It can then refer to the temporary table
where the view's name appears in the query. To see how this would work, consider the
following query:
mysql> SELECT Code, Name FROM Oceania WHERE Name = 'Australia';
Here's how the server might execute it as a temporary table. The temporary table's
name is for demonstration purposes only:
mysql> CREATE TEMPORARY TABLE TMP_Oceania_123 AS
-> SELECT * FROM Country WHERE Continent = 'Oceania';
mysql> SELECT Code, Name FROM TMP_Oceania_123 WHERE Name = 'Australia';