MySQL Learning notes-index creation, deletion

/* Index *//* Index design principles 1. Try to choose a uniqueness index of 2. Order by,group by,distinct for frequent needs, The field of the Union is set at index 3. Sets the index for a field that is commonly used as a query condition 4. Limit the number of indexes 5. Use indexes with a small amount of data, index values are long, and queries are 6 slower. Use prefixes to index 7. Delete indexes that are no longer used, or indexes that are rarely used *//* generally, You should create indexes on these columns, such as:        first, on columns that are often searched, to speed up the search;          second, on the column that is the primary key, force the column to be unique and the structure of the data in the organization table;         Third, These columns are mostly foreign keys, which are often used on connected columns, to speed up the connection;         IV, create an index on a column that often needs to be searched by scope. Because the index is already sorted, its specified range is continuous;         fifth, the index is created on the columns that are often ordered, because the index is sorted so that the query can take advantage of the sorting of the index, Speed up the sort query time;         Six, create indexes on the columns that are often used in the WHERE clause to speed up the judgment of the condition. *//* creating an index when creating a table */--Normal index use testcreate table index1 (Id int,name varchar),sex  Boolean,index (ID)--normal index show create table index1--check the CREATE TABLE statement to see if an index exists and the index name is ' ID ', ok -- Use the explain statement to see if the index is referenced explain select * from index1 where id =1--check the POBoth Ssible_keys and key are ' ID ', stating that the ID index exists and is used--the uniqueness Index CREATE TABLE INDEX2 (id int unique, -- Without a unique constraint, you can create a unique index, but not the ability to improve query speed! Name varchar (), unique index index2_id (ID ASC)  --in ascending order)--full-text index create table  Index3 (Id int,info varchar), Fulltext index index3_info (info)) show create  table index3 --Verify that the full-text index of the InnoDB engine-a single-column index, either a normal index or a unique index, or a full-text index create table index4 (ID  int,subject varchar (+), Index index4_st (subject)  --subject single column 10 length prefix index) show create  table index4--Multi-column index CREATE TABLE INDEX5 (id int,name varchar), Sex char (4), Index index5_ns (Name,sex)--In a multicolumn index, the index is used only if the query condition uses the first field EXPLAIN SELECT * FROM INDEX5  where name= ' Zrz '--possible_keys and key are Index5_ns

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/7F/FB/wKioL1cz0v6wC1n_AABHJVnQoA4723.png "title=" multi-column index, The query refers to the first. png "alt=" Wkiol1cz0v6wc1n_aabhjvnqoa4723.png "/>

Explain select * from index5 where sex= ' boy '--possible_keys and key are null, because the query does not reference the first field-the spatial index, I hear it is not used, skip/* Create an index on an already existing table *//* Add */--directly create normal index use test--Pit CREATE TABLE EXAMPLE0 (ID int (one) default NULL, name varchar () default NULL, sex tinyint (1) de Fault null) engine = InnoDB Default charset = Utf8create index index7_id on EXAMPLE0 (ID)--Create an index named INDEX7_ID in example ID column On show CREATE TABLE Example0

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/7F/FB/wKioL1cz0sSxwpOXAAAyOg-PEaw413.png "title=" Create a normal index on an existing table. png "alt=" Wkiol1cz0ssxwpoxaaayog-peaw413.png "/>

--Modify the table to create a unique index--for the author pits, in the video, some of the tables in the book is not defined in the direct reference .... CREATE TABLE index8 (ID int,name varchar); Create unique index index8_id on index8 (ID)--Here the video differs from the Book Show create TABLE Index 8--Successful creation of a unique index--for the author pits, in the video, some of the tables in the book is not defined in the direct reference. CREATE TABLE index9 (ID int,info varchar ()) Engine=myisamcreate fulltext index index9_info on index9 (info)--Creating full-text index show CREATE TABLE index9--successfully created-for the author pits, in the video, some of the tables in the book is not defined in the direct reference. CREATE TABLE index10 (ID int,address varchar) CREATE INDEX INDEX10_ADDR on index10 (Address (4))--single-column prefix index of length 4 show creat E Table index10--Successfully created

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/7F/FE/wKiom1cz0aLTn2O-AAA1aADyDYg686.png "title=" A single-column index of length 4. png "alt=" Wkiom1cz0altn2o-aaa1aadydyg686.png "/>

--for the author pits, the video inside some of the tables in the book is not defined on the direct reference. Create table index11 (Id int, name varchar),  address varchar (()) Create  index index11_na on index11 (name,address)--Multi-column index created on name,address show create  table index11--successfully created/* Use alter table statement to create an index *//* temporarily skip this experiment, already create index xx on table_ C ()   statement, and no difference is found with Alter table a add index () */alter table a add  INDEX INDEX1 (name ()) Alter table a add index [unique|fulltext] index2 ( attribute_a) alter table a add index index3 (attribute_a,attribute_b)/* Delete index */show  create table index1 --View the name of the index to be dropped, and for iddrop index id on index1 --to remove the ID, Index built on the INDEX1 table 

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/7F/FB/wKioL1cz1KaT1p5-AAA7dZF4nhw324.png "title=" successfully deleted index & #039;id& #039;. PNG "alt=" Wkiol1cz1kat1p5-aaa7dzf4nhw324.png "/>

MySQL Learning notes-index creation, deletion