SQL Getting Started Classic (fifth edition) Ryan Stephens study notes Part V: Performance tuning

16th: Improving performance with indexes

1. CREATE index

Single field index: CREATE INDEX INDEX_NAME on table_name (COLUMN_NAME);
Unique index: Create unique index index_name on table_name (COLUMN_NAME);

Combined index: An index of two or more fields in a table

CREATE INDEX index_name on table_name (COLUMN1,COLUMN2);

2. When to consider setting the index

3, DROP index index_name; Delete Index

4, ALTER index index_name; modifying indexes

20th chapter: Creating and Using views and different names

A view is a virtual table that only exists in memory, does not occupy the actual storage space, does not save the data, it simply refers to the data from the table, it is used in the same way as the table.

1. Usage Scenarios for views

• Simplify data access
  Sometimes the format of the data in the table is not suitable for the terminal query, you can create a series of views.
• As a security policy
  Views can restrict users from accessing only the table-specific fields or records that meet certain criteria.
• Maintain summary data
  If the table on which the summary data is based is updated frequently, it is easier to use a view than to write SQL every time

2. Create a View
This is how the CREATE view is defined in the MySQL documentation:

CREATE [OR REPLACE] [algorithm = {UNDEFINED | MERGE | TempTable}]

[definer = {User | Current_User}] [SQL SECURITY {definer | INVOKER}]
VIEW view_name [(column_list)]
As Select_statement
[With [cascaded | LOCAL] CHECK OPTION]

A common view creation statement is this:

CREATE VIEW v_customers as SELECT * from V_customers;

1. With CHECK OPTION
   It's not a lot of use. It's like a constraint. If this option is used when creating a view, the operation fails when the record inserted or updated into the table does not conform to the view's criteria.
2. Delete a view
   As with deleting a table, DROP you can delete a view using:
   DROP VIEW V_CUSTOMERS;

To create a view from a view:

CREATE TABLE table_name as select column1,colum2 from Table_from_view;

3, different name: table or another name of the view, access can not apply the full name.

Create a different name: "Create [public | private]" synonym synonym_name for table_name|view_name;

Delete different name: drop synonym Synonym_name;

Application: Select Synonym_name; = = SELECT * FROM table_name;

You can access this table without adding the owner's name.

SQL Getting Started Classic (fifth edition) Ryan Stephens study notes Part V: Performance tuning