Mysql modifies tables/fields to add/delete table Indexes

Mysql tutorial Modify Table/field Add/delete table Index

Create table test (blob_col blob, index (blob_col (10); in mysql 5.1, for myisam and innodb tables, the prefix length can reach 1000 bytes. Note that the prefix limit should be measured in bytes, while the prefix length in the create table statement is interpreted as the number of characters. You must consider specifying the prefix length for a column that uses a multi-byte character set.

You can also create fulltext indexes. This index can be used for full-text search. Only the myisam storage engine supports fulltext indexes and only char, varchar, and text columns. The index always applies to the entire column and does not support partial (prefix) indexes.

 

Add Index
Mysql> alter table name add index name (field name 1 [, field name 2…]);

Example: mysql> alter table employee add index emp_name (name );
Index with primary keywords
Mysql> alter table name add primary key (field name );
Example: mysql> alter table employee add primary key (id );
Add an index with unique conditions
Mysql> alter table name add unique index name (field name );
Example: mysql> alter table employee add unique emp_name2 (cardnumber );
Mysql alter syntax usage: view the index of a table
Mysql> show index from table name;

Example: mysql> show index from employee;

Delete An index
Mysql> alter table Name drop index name;

Example: mysql> alter table employee drop index emp_name;

Modify table: add a field: mysql> alter table table_name add field_name field_type;
View the table: mysql> select * from table_name;

Modify the original field name and type: mysql> alter table table_name change old_field_name new_field_name field_type;

Delete field: mysql alter table table_name drop field_name;

Last added:

Multi-column Index
Mysql can create indexes for multiple columns. An index can contain 15 columns. For some column types, You Can index the column prefix (see section 7.4.3, "column Index ").

Multi-column indexes can be considered as an array that contains the sorting of values created by connecting the values of the index column.

Mysql uses multi-column indexes in this way: When you specify a known number for the 1st columns of the index in the where clause, the query is very fast, even if you do not specify the values of other columns.

Assume that the table has the following structure:

Create table test (id int not null, last_name char (30) not null, first_name char (30) not null, primary key (id), index name (last_name, first_name )); the name index is an index for last_name and first_name. An index can be used for queries with last_name or a specified value in a known range of last_name and first_name. Therefore, the name index is used for the following query:

Select * from test where last_name = 'widenius '; select * from test where last_name = 'widenius' and first_name = 'Michael '; select * from test where last_name = 'widenius 'and (first_name = 'Michael' or first_name = 'monty '); select * from test where last_name = 'widenius 'and first_name> = 'M' and first_name <'n'; however, the name index is not used for the following query:

Select * from test where first_name = 'Michael '; select * from test where last_name = 'widenius' or first_name = 'Michael ';