The MySQL index is used to quickly look for records with specific values, and all MySQL indexes are saved as B-trees. If there is no index, MySQL must start scanning all records of the entire table from the first record until it finds a record that meets the requirements. The higher the number of records in the table, the higher the cost of this operation. If an index has been created on a column that is a search condition, MySQL can quickly get to where the target record is without scanning any records. If a table has 1000 records, finding records by index is at least 100 times times faster than sequential scan records.
General analysis
PRIMARY, INDEX, UNIQUE these 3 kinds are a kind of
PRIMARY primary key. is unique and cannot be empty.
Index, normal
Unique index. Duplicates are not allowed.
Fulltext is a full-text index that is used to retrieve textual information in an article.
MySQL index type: PRIMARY, what's the difference between index,unique,fulltext,spaial? What are the applicable occasions?
For example, for example, you are making a membership card system for a mall.
This system has a membership table
The following fields are available:
Member ID INT
Member name VARCHAR (10)
Member ID number VARCHAR (18)
Member Phone VARCHAR (10)
Member Address VARCHAR (50)
Member Note Information TEXT
Then this membership number, as the primary key, using PRIMARY
Member name if you want to index, then it is the normal index
Member ID number if you want to index, then you can choose unique (unique, not allowed to repeat)
Member notes information, if need to build index, you can choose Fulltext, full-text search.
Fulltext, however, works best when it comes to searching for a long post.
Used in relatively short text, if the one or two lines of the word, the normal INDEX can also.
Create INDEX usage Instructions
Normal index
This is the most basic type of index, and it has no limitations such as uniqueness.
Create indexes, such as the name of the CREATE INDEX < index > on tablename (List of columns);
Modify the table, such as ALTER TABLE TableName ADD index [name of index] (list of columns);
Specify an index when creating a table, such as CREATE TABLE TableName ([...], index [name of indexed] (List of columns));
Uniqueness Index
This index is basically the same as the previous "normal index", but there is one difference: all the values of an indexed column can only occur once, that is, they must be unique.
MySQL PRIMARY key:
The MySQL primary key is a unique index, but it must be specified as "PRIMARY key".
For example "CREATE TABLE tablename ([...], PRIMARY KEY (List of columns)"); ”。 However, we can also add the primary key by modifying the table, such as "ALTER table tablename Add PRIMARY key (List of columns); ”。 There can be only one primary key per table.
Full-Text indexing
MySQL supports full-text indexing and full-text retrieval starting from version 3.23.23. In MySQL, the index type of the full-text index is fulltext. A full-text index can be created on a varchar or text-type column. It can be created by the CREATE TABLE command or by the ALTER TABLE or CREATE INDEX command. For large datasets, it is faster to create a full-text index by using the ALTER TABLE (or CREATE INDEX) command than to insert the record into an empty table with a full-text index.
Single-column and multicolumn indexes
Create indexes such as the name of the Create UNIQUE Index < index > on tablename (List of columns);
Modify the table, such as ALTER TABLE TableName ADD UNIQUE [index name] (List of columns);
Specify indexes when creating tables, such as CREATE TABLE TableName ([...], UNIQUE [index name] (List of columns)
);
Common indexes in MySQL, master, unique, full-text index differences