mysql modify table / field add / delete table index

mysql tutorial to modify the table / field to 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 can be up to 1000 bytes in length. 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. Must be considered when specifying the prefix length for columns that use multi-byte character sets.

You can also create a fulltext index. The index can be used for full-text search. Only the myisam storage engine supports the fulltext index and is only for char, varchar, and text columns. The index is always on the entire column, local (prefix) index is not supported

Plus index
mysql> alter table table name add index index name (field name 1 [, field name 2 ...]);

Example: mysql> alter table employee add index emp_name (name);
Add the main keyword index
mysql> alter table table name add primary key (field name);
Examples: mysql> alter table employee add primary key (id);
Add a unique index of restrictions
mysql> alter table table name add unique index name (field name);
Example: mysql> alter table employee add unique emp_name2 (cardnumber);
Mysql alter syntax: Check the index of a table
mysql> show index from table name;

Example: mysql> show index from employee;

Delete an index
mysql> alter table table name drop index index name;

Example: mysql> alter table employee drop index emp_name;

Modify the table: Increase the 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 the field: mysql alter table table_name drop field_name;

Finally add a little

Multi-column index
mysql can create indexes for multiple columns. An index can include 15 columns. For some column types, you can index the column prefixes (see Section 7.4.3, "Column Indexes").

Multi-column indexes can be treated as an array containing the sorted values ​​of the values ​​created by concatenating indexed columns.

mysql uses multiple column indexes in such a way that when you specify a known amount for the first column of an index in the where clause, the query is fast, even if you do not specify values ​​for other columns.

Suppose 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)); name index is an index on last_name and first_name . The index can be used for the last_name, or for the last_name and first_name query in the specified range of values. 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';