6. mysql Index type

1. General Index

This is the most basic index, it has no restrictions, such as the previous index created for the title field is a normal index, myiasm in the default index of the Btree type, but also in most cases we use the index.

– Create indexes directlyCREATE INDEXIndex_name on Table(column(length)) – The way the table structure is modified to add an indexALTER TABLEtable_nameADD INDEXIndex_name on(column(length)) – Create the index at the same time as the table is createdCREATE TABLE`Table' (' ID 'int( One) not NULLauto_increment, ' title 'Char(255)CHARACTER SETUTF8 COLLATE utf8_general_ci not NULL, ' content 'text CHARACTER SETUTF8 COLLATE utf8_general_ciNULL, ' time 'int(Ten)NULL DEFAULT NULL ,PRIMARY KEY(' id '),INDEXindex_name (length)) – Delete indexDROP INDEXIndex_name on Table

2. Unique index

Like a normal index, the difference is that the value of the indexed column must be unique, but it allows for a null value (note differs from the primary key). If it is a composite index, the combination of column values must be unique, similar to the creation method and the normal index.

– Create a unique indexCREATEUNIQUEINDEXIndexName onTable(column(length)) – Modify table structureALTERTABLEtable_nameADDUNIQUEIndexName on(column(length)) – Specify directly when creating a tableCREATETABLE`Table' (' ID 'int( One) notNULLauto_increment, ' title 'Char(255)CHARACTERSETUTF8 COLLATE utf8_general_ci notNULL, ' content 'textCHARACTERSETUTF8 COLLATE utf8_general_ciNULL, ' time 'int(Ten)NULLDEFAULTNULL ,PRIMARYKEY(' id '),UNIQUEIndexName (title (length)));

3. Full-text index (fulltext)

MySQL supports full-text and full-text indexing starting from version 3.23.23, fulltext indexes are available only for MyISAM tables; they can be created as part of a CREATE TABLE statement from char, varchar, or text columns, or subsequently using the ALTER TABLE or CREATE index is added. For larger datasets, enter your data into a table without a Fulltext index, and then create an index that is faster than entering the data into an existing Fulltext index. But remember, for a large data table, generating a full-text index is a very expensive way to consume hard disk space.

– Create tables that are suitable for adding full-text IndexesCREATETABLE`Table' (' ID 'int( One) notNULLauto_increment, ' title 'Char(255)CHARACTERSETUTF8 COLLATE utf8_general_ci notNULL, ' content 'textCHARACTERSETUTF8 COLLATE utf8_general_ciNULL, ' time 'int(Ten)NULLDEFAULTNULL ,PRIMARYKEY(' id '), fulltext (content)); – Modify table structure add full-text indexALTERTABLEArticleADDfulltext Index_content (content) – Create an index directlyCREATEFulltextINDEXIndex_content onArticle (content)

4., single-row index, multi-column index
Multiple single-column indexes differ from the query effect of a single multicolumn index because MySQL can use only one index when executing a query, and one of the most restrictive indexes is selected from multiple indexes.
5., combined index (leftmost prefix)
Usually use the SQL query statements generally have more restrictive conditions, so in order to further extract the efficiency of MySQL, we should consider the establishment of a composite index. For example, the previous table establishes a composite index for title and time: ALTER Table Article ADD index Index_titme_time (title (), Time (10)). Creating such a composite index is actually equivalent to establishing the following two sets of composite indexes:

–title,time
–title

Why is there no such combination index as time? This is because the MySQL composite index is the result of the "leftmost prefix". The simple understanding is only from the left to the beginning of the combination. Not all queries that contain these two columns will use the combined index, as shown in the following SQL:

– Use the index aboveSELECT* fromArticle Whree title='Test' andTime=1234567890;SELECT* fromArticle Whree Utitle='Test'; – Do not use the index aboveSELECT* fromArticle Whree time=1234567890;

6. mysql Index type