Mysql index learning notes

Mysql index learning Notes 1. advantages and disadvantages of indexes: speeding up the query speed and downgrading: it affects the rate of table deletion and modification operations and increases the file size (the index file may be larger than the data file, before importing a large amount of data to the database, you should temporarily Delete the index, and then create a unified index after the data is imported. Www.2cto.com 2. index creation principle (1) index failure (2) the index should be created in the field that needs to be frequently used as the where condition (3) the index field should be Hash 3. index type (1) normal index: accelerate the query speed; (2) unique index: the value cannot be repeated; (3) primary key index (primary key ): the primary key is automatically indexed and the value is unique, but the unique index is not necessarily the primary key. A table can have multiple unique indexes, but only one primary key is allowed. (4) full text index (fulltext index): By default, this index is of little significance to Chinese. 4. syntax (1) view the table index: show index from tbname; (2) create an index: alter table tbname add index [unique | fulltext] indexname columnname; alter table tbname add primary key columnname; // you cannot add a primary key. Only one primary key can be added. (3) delete an index: alter table tbname drop index indexname; alter table tbname drop primary key; // Delete the primary key