CX Optimization Agency Message Testing Blog Search Start 7-day trial for $1 Upskilling Playbooks Teams Community Find a job Resources Help

Create views in MySQL

Use case

Combine commonly used select queries from multiple tables into a single view and save time.

Upgrade for unlimited access to thousands of playbooks on increasing traffic, improving ROAS and more.

Start 7-day trial for $1

1. Plan the view structure by identifying which columns from which tables need to be included, and use the SELECT statement to make sure all necessary columns are included.

Plan out the columns that need to be included in the view and what tables they would be obtained from.

Use a SELECT statement to make sure all columns are included, and add or remove any columns as necessary. For example:

SELECT
 table1.column1,
 table1.column2,
 table2.column3,
 table2.column4
FROM
 table1,
 table2
WHERE
 table1.column1 = table2.column1
AND table1.column3 = table2.column5
Join the discussion on how to complete this step.

2. Use the CREATE OR REPLACE VIEW statement to create, name, and update a view.

For example, where My_sample_view is the name of the view:

CREATE OR REPLACE VIEW My_Sample_View AS
SELECT
 table1.column1,
 table1.column2,
 table2.column3,
 table2.column4
FROM
 table1,
 table2
WHERE
 table1.column1 = table2.column1
AND table1.column3 = table2.column5;
Join the discussion on how to complete this step.

3. Use the SELECT statement to execute the view and verify output and performance.

Use the following syntax to check the view’s output:

SELECT * from My_Sample_View;
Join the discussion on how to complete this step.

4. Use the CREATE INDEX statement to create indexes on columns that have a numeric or long string output to improve performance when executed.

Executing views should take no longer than 30 seconds. If they do, you may need to create indexes on columns that have numeric or long string outputs.

For example:

CREATE INDEX ind_column1 ON table1(column1)
Join the discussion on how to complete this step.

5. Use the ALTER VIEW statement to modify or update the already created view without dropping it.

For instance, the following example shows how to execute the view while excluding table2.column4.

ALTER VIEW My_Sample_View AS
SELECT
 table1.column1,
 table1.column2,
 table2.column3
FROM
 table1,
 table2
WHERE
 table1.column1 = table2.column1
AND table1.column3 = table2.column5;
Join the discussion on how to complete this step.

Current Playbook:

Create views in MySQL

Oct 21, 2021

0 votes

Request a playbook

Get unlimited access

Thousands of playbooks on increasing traffic, improving ROAS and more.

Start 7-day trial for $1