My SQL View

Why we use view in database.

The following are some reasons why database designers, database administrators, developers, and other database folks might want to implement in views in our database or application,

A, Query abstraction

B, Limited access

C, Backward-compatibility

D, Security

E, Development

F, Virtual fields

 

View in My SQL

THe MySQL server processes queries of views in two different ways:

A, MySQL creates a temporary table of the data results from the view's defining SELECT statement, and then executes the incoming SQL against the temporary table.

B, MySQL combines the incoming SQL statement with the view's defining SELECT statement, creating a new, single SQL statement. This SQL statement is executed against the tables.

 

The CREATE Statement

CREATE [OR REPLACE] [<algorithm attributes>] VIEW [database.] <name>[(<columns>)] AS <SELECT statement> [<check options>]

 

 

Algorithm Attributes

 

MERGE

TEMPTABLE

UNDEFINED

 

 

Performance of Views

 

   First, it's important to remember that the performance of a view is not going to be any better than the performance of the underlying tables. If your tables aren't optimized, or are organized poorly, a view to clean things up might help the interface, but it won't help performance of your queries.

   Second, views rely on the indexes of the underlying tables. If your views is created on a table with ten million records, using a WHERE clause referencing columns without indexes, the view will perform just as poorly as the query. For the best performance, indexes on underlying tables should be designed to match the SELECT statement used in defining views.