MySQL® Administrator's Bible

Chapter 8. MySQL Views

IN THIS CHAPTER

• Defining views

• Changing a view definition

• Replication and views

A normalized schema is the best starting point for any application. OLTP (on-line transaction processing) and OLAP (on-line analytics processing) have often used separate schemas. Batch imports and the Extract-Transform-Load (ETL) process are commonly used to migrate data from a transactional schema to an analytical one. However, this takes time and effort, and introduces a delay in data getting to a reporting server.

The need for more real-time analysis is growing stronger. In addition, replication in MySQL is widespread and easy. Views are tools that assist in denormalizing data, such as for analysis, without changing the underlying system for transaction processing.

Views also allow for simplicity, abstraction, added security, and easier schema migration. Views do not necessarily improve performance, however.

Views allow for small schema changes that can have big effects without having to change a table with ALTER TABLE. Views can be used to:

• Restrict access to a subset of rows

• Simplify queries

• Simulate check constraints

Defining Views

A view is a way to define a dynamic virtual table based on an SQL statement. It can be queried like a regular table can, and views appear in the TABLES table of the INFORMATION_SCHEMA database (see Chapter 22). A view is an object associated with a database, similar to a regular table. In fact, the SHOW TABLES and SHOW TABLE STATUS commands return tables ...