Views in MySQL : MySQL Views

Summary: in this tutorial, you are going to learn about MySQL View. We will explain how MySQL implements views.

MySQL supports database views or views since version 5.X. In MySQL, almost features of views conform to the SQL: 2003 standard. MySQL process queries to the views in two ways:

  • MySQL creates a temporary table based on the view definition statement and then executes the incoming query on this temporary table.
  • First, MySQL combines the incoming query with the query defined the view into one query. Then, MySQL executes the combined query.

MySQL supports version system for views. Each time when the view is altered or replaced, a copy of the existing view is back up in arc (archive) folder which resides in a specific database folder. The name of back up file is view_name.frm-00001. If you then change the view again, MySQL will create a new backup file named view_name.frm-00002.

MySQL also allows you to create a view of views. In the SELECT statement of view definition, you can refer to another views.

If the cache is enabled, the query against a view is stored in the cache. As the result, it increases the performance of the query by pulling data from the cache instead of querying data from the underlyingtables.

 

Related Posts

  • 52
    Summary: in this tutorial, you will learn how to create views in MySQL by using the CREATE VIEW statement. Introduction to CREATE VIEW statement The syntax of creating a view in MySQL is as follows:   1234 5   CREATE[ALGORITHM = {MERGE  | TEMPTABLE | UNDEFINED}]VIEW [database_name].[view_name]AS [SELECT  statement] Algorithms The algorithm attribute allows you to control…
    Tags: view, mysql
  • 48
    I connect to mysql from my Linux shell. Every now and then I run a SELECT query that is too big. It prints and prints and I already know this is not what I meant. I would like to stop the query. Hitting Ctrl+C (a couple of times) kills mysql…
    Tags: mysql, query
  • 41
    Summary: in this tutorial, you will learn how to manage views in MySQL including displaying, modifying and removing views. Show view definition in MySQL MySQL provides the SHOW CREATE VIEW statement that helps you show view definition. The following is the syntax of the SHOW CREATE VIEW statement:   1 SHOW CREATE VIEW [database_name].[view_ name]; To…
    Tags: view
  • 38
    Easiest way to do this is to add a UNIQUE index on the column. When you write the ALTER statement, include the IGNORE keyword. Like so: ALTER IGNORE TABLE jobs ADD UNIQUE INDEX idx_name (site_id, title, company); This will drop all the duplicate rows. As an added benefit, future INSERTs…
    Tags: mysql, query
  • 37
    A Shell script for killing slow MySQL queries: #!/bin/sh # Credentials for a MySQL user with PROCESS, SUPER permissions USERNAME= PASSWORD= # MySQL Server location HOST= PORT=3306 TIMEOUT=60 # 1 minute TARGET_USER= # MySQL user to monitor MYSQL="mysql -u $USERNAME --password=$PASSWORD -h $HOST -P $PORT -B" $MYSQL -N -e 'SHOW…
    Tags: mysql, query

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: