Mysql Data index create index

What is an index?

Indexes are used to quickly search for records with specific values. All MySQL indexes are saved as B-trees. If no index exists, MySQL must scan all the records of the entire table from the first record until the required records are found. The more records in the table, the higher the operation cost. If an index has been created on the column used as a search condition, MySQL can quickly obtain the location of the target record without scanning any records. If the table has 1000 records, the index search records should be at least 100 times faster than the Sequential Scan records. Index type:Normal index: This is the most basic index type and has no limitations such as uniqueness. Unique index: It is basically the same as a normal index, but all index columns can only appear once to maintain uniqueness. Primary key: a primary key is a unique index, but must be specified as a "primary key ". Full-text indexing: MYSQL supports full-text indexing and full-text retrieval from 3.23.23. In MYSQL, the full-text index type is FULLTEXT. Full-TEXT indexes can be created on VARCHAR or TEXT columns. Single-Column index and multi-column IndexAn index can be a single-column index or multiple-column index. Using indexes for related columns is the best way to improve the performance of SELECT operations. 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. 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, name index NoUsed for the following query:

SELECT * FROM test WHERE first_name='Michael'; SELECT * FROM test    WHERE last_name='Widenius' OR first_name='Michael';