Index-mysql index creation, viewing, deletion, and use examples

MySQL index create, view, delete, and use examples

1. Create an index:

ALTER table is used to create a normal index, a unique index, or a primary key index.

ALTER TABLE ADD INDEX index_name (column_list) ALTER TABLE ADD UNIQUE (column_list) ALTER TABLE ADD PRIMARY KEY (column_list)

CREATE Index to add a normal or unique index to a table.

CREATE INDEX  on table_name (column_list) CREATE UNIQUE INDEX  on table_name (column_list)

2. Delete the index:

DROP INDEX  on Talbe_name ALTER TABLE DROP INDEX index_name ALTER TABLE DROP PRIMARY KEY

3. View the index:

MySQL>index from tblname;mysql> from Tblname;

SQL code online format beautification tool : Http://tools.jb51.net/code/sqlcodeformat
4. Usage scenarios for the index:

=========================== The following is an indexed usage scenario (SQL Server) =================================

[1]. A random discussion of database indexing

1. Create a table and insert data

Create a test database in SQL Server2008, then create the database table and insert the data, the SQL code is as follows:

UseTestIFEXISTS (SELECT*FromInformation_schema. TABLESWHERE table_name=‘Emp_pay‘)DROPTABLEEmp_payGOUseTestIFEXISTS (SELECT NameFromSys.indexesWHERE Name=‘Employeeid_ind‘)DROPINDEXEmp_pay.employeeid_indGOUseTestGOCREATETABLEEmp_pay (EmployeeIDIntNotNull, Base_payMoneyNotNull, commissionDecimal2,2)NotNull)INSERTEmp_payVALUES (1,500,.10)INSERTEmp_payVALUES (2,1000,.05)insert Emp_pay values ( Span style= "color: #800000; Font-weight:bold ">6, 800,. 07) insert Emp_pay values (5, 1500,. 03)  Insert Emp_pay values (9, 750,. 06)               

After executing the above SQL code, we will find an extra Emp_pay table in the test database, the contents of the database table as shown:

2. No index Lookup

From what we can see, the order in which the data is stored in the database is consistent with the order in which we inserted. Next we query the Employeeid=5 field and execute the following SQL code:

Use Testwhere EmployeeID=5   

In SQL SERVER MANAGEMENT Studio, we clicked on "Show estimated query plan", which appears as shown in the query plan diagram:

The contents of the table scan are:

3. Create an index

Next we add a clustered unique index to the above table, with the following code:

OFFINDEXon emp_pay (EmployeeID)GO   

After executing the code that created the index above, we query the Emp_pay data content again, as shown in:

From what we can find the data content has been sorted according to EmployeeID.

We continue with the previous query on employeeid=5, click on "Show Estimated execution plan" and appear as shown:

The clustered index looks for the content:

Summarize:

When we create an index for a field in a database table and use such a field in the WHERE clause in the query statement, the query efficiency is improved, and the efficiency of the relational query is not obvious because of the amount of data in our experiment.

Add

The index we added above is a unique clustered index, so when the inserted data is duplicated in the EmployeeID field, an error occurs. If we have duplicate data fields before we create the index, we cannot create a unique index.

Sorting after creating an index (PS:2012-5-28)

Execute the following SQL statement

Set EmployeeID=where EmployeeID=1;   

Then we execute the full table query again, and we find that the query results are as follows:

As soon as we update the EmployeeID, the results of the last update are sorted in ascending order of the EmployeeID values. This is because we created the index on the EmployeeID.

Delete index (PS:2012-6-4)

We can either delete the index through SQL Server Management Studio or we can delete the index through the SQL statement, assuming we want to delete the index Employeeid_ind created earlier, the SQL statement looks like the following code:

On Emp_pay;

Index-mysql index creation, viewing, deletion, and use examples