6. mysql Index type

Source: Internet
Author: User
Tags create index mysql index

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:


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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.