MySQL index classification and use

One, MySQL: index in B-Tree format save

The memory storage engine can select either a hash or a btree index, and the hash index can only be used for = or <=> equality comparisons.

1. Normal index: CREATE index on TableName (List of columns)

ALTER TABLE tablename Add index (List of columns)

CREATE TABLE TableName ([...], index [indexname] (List of columns)

2. Uniqueness Index: Create unique index

Alter ... add unique

Primary key: A unique index that must be specified as primary key

3, Full-text indexing: from 3.23.23 Version to support Full-text indexing and Full-text search, Fulltext,

Can be created on a column of char, varchar, or text type.

4, single row index, multiple column index:

Multiple single-column indexes have different query effects from a single, multiple-column index because:

When executing a query, MySQL can only use one index, and selects one of the most restrictive indexes from multiple indexes.

5, the leftmost prefix (leftmost prefixing): Multiple-column index, such as: Fname_lname_age index, the following search conditions MySQL will use

Fname_lname_age index: Firstname,lastname,age;firstname,lastname;firstname, other circumstances will not be used.