MySQL index use

　　

1. Start by randomly creating a table with the following SQL statement:

CREATE TABLE IF not EXISTS ' student ' (

' id ' int (one) not NULL auto_increment COMMENT ' School Number ',

' Name ' varchar (+) not NULL DEFAULT ' COMMENT ' name ',

' Sex ' tinyint (1) not NULL COMMENT ' sex ',

' Age ' tinyint (2) is not NULL COMMENT ' ages ',

' class ' varchar (+) not NULL DEFAULT ' COMMENT ' class ',

PRIMARY KEY (' id ')

Engine=myisam DEFAULT charset=utf8 comment= ' student table ';

2. As you can see, in the SQL statement that created the table, a primary key index has been established and the index in the table is viewed: Show index from ' student '

3. Of course, we can also add other indexes on the basis, such as a unique index. Assuming that each student's name cannot be duplicated, a unique index can be added to the Name field:

ALTER TABLE ' student ' ADD UNIQUE ' stu_name ' (' name ');

At this point, look again at the index in the table, show index from ' student '

4. Then add a normal index to the class:

ALTER TABLE ' student ' ADD INDEX ' Stu_class ' (' class ');

View index in table, show index from ' student '

5. Next, delete the index, remove the unique index and the normal index:

ALTER TABLE ' student ' DROP INDEX ' stu_name ';

ALTER TABLE ' student ' DROP INDEX ' stu_class ';

Then look at the index in the table, show index from ' student '

MySQL index use