How to add indexes to MySQL

Here is a brief introduction to indexes:

The purpose of adding indexes is to improve database query performance. Indexes are the most cost-effective things. There is no need to add memory, change programs, or adjust SQL. Just execute a correct create index and the query speed may increase by hundreds or thousands of times. This is tempting, but there is no free lunch. The increase in query speed comes at the expense of the speed of insert, update, and delete. Moreover, the index size is generally one-third of the data. In addition, the index needs to be loaded into the memory. If all fields are indexed, it will sacrifice memory. Therefore, it is necessary to add indexes appropriately.

Here is a brief introduction to the commonly used indexes in MySQL:

Before adding an index, it is best to check which indexes already exist in the table: show index from table name;

1. Primary key index

Note: There can only be one primary key index in a table, but you can add multiple indexes such as unique index, normal index, and full-text index.

Primary key index: It is usually added when the table is created. For example, id is usually the primary key index plus automatic increment.

alter table table_name add primary key (column name);

Characteristics of the primary key index: cannot be empty and unique.

2. Ordinary index

Create a normal index: alter table table_name add index 索引名(column1,column2);

3. Unique Index

Create a unique index: ALTER TABLE `table_name` ADD UNIQUE (`column`)；

The difference between a unique index and a primary key index:

Unique index: can have multiple nulls but data content cannot be repeated

Primary key index: cannot be null and the content must be unique.

The difference between the two is that the primary key index cannot be null and the unique index can have multiple nulls. The rest are the same.

4. Full-text indexing

Full-text indexing is only available for MyISAM (InnoDB also supports full-text indexing after MySQL 5.6) [5.7 does not support MyISAM]

Full-text indexing is mainly aimed at text files, such as articles and titles.

Create a full-text index when you create a table:

    CREATE TABLE `article` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `title` varchar(200) DEFAULT NULL,
 ` content` text,
      PRIMARY KEY (`id`),
      FULLTEXT KEY `title` (`title`,`content`)
      ) ENGINE=MyISAM (InnoDB also supports full-text indexing after 5.6, but 5.7 does not support the MyISAM engine) DEFAULT CHARSET=utf8;

To create a full-text index on an existing table:

ALTER TABLE article ADD FULLTEXT INDEX fulltext_article(title,content);

There are also some things to note when using the full-text index after creating it:

It is well known that fuzzy query in the database is performed using the like keyword, for example:

SELECT * FROM article WHERE content LIKE '%query string%';

So, do we use full-text indexes in the same way? Of course not. We must use a special syntax to query using full-text indexes. For example, if we want to search the full text of the specified query string in the title and content columns of the article table, we can write the SQL statement as follows:

SELECT * FROM article WHERE MATCH(title,content) AGAINST ('query string');

Strongly note: MySQL's built-in full-text index can only perform full-text searches on English texts, and currently cannot perform full-text searches on Chinese texts. If we need to perform full-text search on text data including Chinese, we need to use Sphinx/Coreseek technology to process Chinese.

Note: Currently, when using the full-text index provided by MySQL, if the query string is too short, the expected search results will not be obtained. The default minimum length of a word that can be found in a MySql full-text index is 4 characters. In addition, if the query string contains stop words, then the stop words will be ignored.

If possible, try to create the table and insert all the data before creating the full-text index, rather than creating the full-text index directly when creating the table, because the former is more efficient than the latter.

Delete index SQL statement: alter table table_name drop index 索引名;

After the brief introduction above, which fields should be indexed?

1. Indexes should be created for frequently queried fields.

2. Indexes should not be created for fields that are updated very frequently.

3. Fields with poor uniqueness, such as the gender field, should not be indexed.

4. Fields that do not appear after the where condition should not be indexed.

An index should be created if the following conditions are met:

1. Frequently queried fields, which often appear after the where condition, should have indexes created.

2. For fields that are not updated frequently, you can create indexes.

Notes on using indexes

1. For a created multi-column index, the index will generally be used as long as the query condition uses the leftmost column.

• For example, we added a composite index to title and content
• select * from table_name where title = 'test'; the index will be used
• select * from table_name where content = 'test'; index will not be used

2. For queries using LIKE, if the query is '%a', the index will not be used, but like 'a%' will use the index. You cannot use variable values ​​such as % and _ at the beginning

3. If there is an or in the condition, it will not be used even if the condition has an index.

4. If the column type is a string, be sure to quote the data in the condition.

• Check the usage of the index: show status like'Handler_read%';
• handler_read_key: The higher the value, the better. A higher value indicates the number of times the index is used for query.
• handler_read_rnd_next: The higher this value is, the less efficient the query is.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links