MYSQL entry 9: simple indexing operations

MYSQL Quick Start 9: simple indexing operations http://www.bkjia.com/database/201212/173868.htmlMYSQL Entry 2: Search Using Regular Expressions http://www.bkjia.com/database/201212/173869.htmlMYSQL Entry 3: Full Text Search http://www.bkjia.com/database/201212/173873.htmlMYSQL Entry 4: MYSQL Data Types http://www.bkjia.com/database/201212/175536.htmlMYSQL Entry 5: MYSQL Character Set http://www.bkjia.com/database/201212/175541.htmlMYSQL Getting started 6: MYSQL Operators http://www.bkjia.com/database/201212/175862.htmlMYSQL Entry 7: MYSQL common functions http://www.bkjia.com/database/201212/175864.htmlMYSQL Step 8: basic database and table operations http://www.bkjia.com/database/201212/175867.html 1. CREATE an INDEX www.2cto.com common mysql index types include: 1. create index idx_name ON table_name (table_col (length); If the INDEX field is of the CHAR or varchar type, length can be less than the actual length of the field. If it is BLOB or TEXT, length must be specified. Mysql> create index idx_name on user (name (10); mysql> show index from user; + ------- + ------------ + ---------- + -------------- + upper + ----------- + | Table | Non_unique | Key_name | primary | Column_name | Collation | + ------- + ------------ + ---------- + upper + ----------- + | user | 1 | idx_name | 1 | name | A | + ------- + ------------ + ---------- + -------------- + ----------- -- + ----------- + 2. unique index create unique index idx_name ON table_name (table_col (length); it is similar to a common INDEX, but the difference is that its INDEX column value must be UNIQUE, however, null values are allowed. Mysql> create unique index idx_cn_name_u on user (cn_name (20); mysql> show index from user; + ------- + ------------ + bytes + -------------- + bytes + ----------- + | Table | Non_unique | Key_name | primary | Column_name | Collation | + ------- + ------------ + bytes + -------------- + ----------- + | user | 0 | idx_cn_name_u | 1 | cn_name | A | user | 1 | idx_name | 1 | Name | A | + ------- + ------------ + --------------- + -------------- + ------------- + ----------- + 3. alter table table_name add primary key (table_col ); it is a special unique index and does not allow null values. A table can have only one primary key index. Mysql> alter table user add primary key (id); mysql> show index from user; + ------- + ------------ + bytes + -------------- + ------------- + | Table | Non_unique | Key_name | PRIMARY | Column_name | + ------- + ------------ + PRIMARY + | user | 0 | PRIMARY | 1 | id | user | 0 | idx_cn_name_u | 1 | cn_name | user | 1 | idx_name | 1 | name | + ------- ----- + --------------- + ------------ + ------------- + 4. create index idx_name ON table_name (table_col_1, table_col_2,..., table_col_n); multiple columns can be used as INDEX columns. Mysql> create index idx_name_sex on user (name, sex); mysql> show index from user; + ------- + ------------ + bytes + -------------- + bytes + ----------- +-| Table | Non_unique | Key_name | primary | Column_name | Collation | + ------- + ------------ + upper + -------------- + upper + ----------- -| user | 0 | PRIMARY | 1 | id | A | user | 0 | idx_cn_name_u | 1 | cn_name | A | | User | 1 | idx_name | 1 | name | A | user | 1 | idx_name_sex | 1 | name | A | user | 1 | idx_name_sex | 2 | sex | A | + ------- + ------------ + --------------- + -------------- + ------------- + ----------- +-www.2cto.com 2. Delete the index drop index idx_name on table_name; alter table table_name drop index idx_name; alter table table_name drop primary key; mysql> alter table user drop primary key; mysql> show Keys from user; + ------- + ------------ + hour + -------------- + ------------- +-| Table | Non_unique | Key_name | hour | Column_name | + ------- + ------------ + hour + -------------- + --------------- +-| user | idx_cn_name_u | 1 | cn_name | user | 1 | idx_name | 1 | name | user | 1 | idx_name_sex | 1 | name | user | 1 | idx_name_sex | 2 | sex | + ------- + ------------ + ------ --------- + -------------- + ----------- +-3. view the index show index from table_name; show keys from table_name; view a complete output of the INDEX Statement, which is similar to the following: mysql> show keys from user; + ------- + ------------ + hour + -------------- + hour + --------- + hour + ---------- + -------- + ------ + ------------ + --------- + | Table | Non_unique | Key_name | hour | Column_name | Collation | Cardinality | sub_part | Packed | Null | Index_type | Comment | + ------- + ------------ + response + -------------- + ------------- + ----------- + ------------- + ---------- + -------- + -------------- + ----------- + | user | 0 | limit | | cn_name | A | NULL | 20 | NULL | YES | BTREE | user | 1 | idx_name | 1 | name | A | NULL | 10 | NULL | YES | BTREE | | user | 1 | idx_name_sex | 1 | name | A | NULL | YES | BTREE | user | 1 | idx_name_sex | 2 | sex | A | NULL | YES | BTREE | + ------- + ------------ + ------------- + -------------- + ------------- + ----------- + ------------- + ---------- + -------- + ------ + ------------ + --------- + where: table: Table Name Non_unique: if the index cannot contain duplicate words, it is 0. If yes, it is 1. Key_name: name of the index. Seq_in_index: the column serial number in the index, starting from 1. Column_name: column name. Collation: How the column is stored in the index. In MySQL, there are values 'A' (ascending) or NULL (unclassified ). Cardinality: the number of unique values in the index. You can update analyze table or myisamchk-a by running analyze table. The base number is counted based on the statistical data stored as an integer. Therefore, this value is not required to be accurate even for small tables. The larger the base, the larger the chance for MySQL to use the index for union. Sub_part: If a column is partially indexed, it is the number of indexed characters. If the entire column is indexed, the value is NULL. Packed: indicates how the keywords are compressed. If it is not compressed, It is NULL. Null: If the column contains NULL, YES is included. If NO, the column contains NO. Index_type: The index type used (BTREE, FULLTEXT, HASH, RTREE ). Comment: Index description.
Iv. Notes for using indexes 1. The index does not contain any column with a NULL value, as long as the column contains a NULL value, it will not be included in the index, if a column in a composite index contains NULL values, this column is invalid for this composite index. Therefore, do not set the default value of a field to NULL when designing a database. 2. Use a short index to index a column. If possible, specify a prefix length. For example, if a CHAR (255) Column exists and multiple values are unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only increase query speed, but also save disk space and I/O operations. 3. Do not perform operations on columns. This will cause index failure and scan the entire table. 4. Do NOT use the NOT and <> operations 5. Index column sorting MySQL queries only use one index. Therefore, if an index is already used in the where clause, therefore, columns in order by will not use indexes. Therefore, do not use the sorting operation when the database's default sorting can meet the requirements. Try not to include the sorting of multiple columns. It is best to create a composite index for these columns if necessary.