MySQL Build index

The common rules for indexing are as follows:

1, the table's primary key, the foreign key must have the index;
2, the data volume of more than 300 of the table should be indexed;
3. Tables that are often connected to other tables should be indexed on the connection field;
4. Fields that often appear in the WHERE clause, especially for large tables, should be indexed;
5, the index should be built on the field of high selectivity;
6, the index should be built on the small section, for large text fields or even long fields, do not build index;
7, the establishment of composite index needs to be carefully analyzed; try to consider using single-field index instead of: A, the correct selection of the main column field in the composite index, generally is a better choice of fields; B, how many fields of a composite index often appear in the WHERE clause in and? Is there very little or no single-field query? If so, you can create a composite index, otherwise consider the single-field index; C, if the compound index contains fields that often appear separately in the WHERE clause, it is decomposed into multiple one-field indexes; D, if the composite index contains more than 3 fields, consider the necessity, consider the reduction of composite fields; E, If you have a single field index and a composite index on these fields, you can generally delete the composite index;
8, frequent data operation of the table, do not set too many indexes;
9. Delete useless indexes and avoid negative impact on execution plan; These are some common criteria for indexing. Word, the establishment of the index must be cautious, the need for each index should be carefully analyzed, to establish the basis. Because too many indexes and inadequate, incorrect indexes are not good for performance: Each index established on the table increases the storage overhead, and the index increases processing overhead for insert, delete, and update operations. In addition, too many composite indexes, in the case of single-field index, generally have no value; Conversely, it also reduces performance when data is being deleted, especially for frequently updated tables, with greater negative impact
1. Add primary key (primary key index)
Mysql>alter TABLE ' table_name ' ADD PRIMARY KEY (' column ')
2. Add unique (unique index)
Mysql>alter TABLE ' table_name ' ADD UNIQUE (
' Column '
)
3. Add index (normal index)
Mysql>alter TABLE ' table_name ' ADD INDEX index_name (' column ')
4. Add fulltext (full-text index)
Mysql>alter TABLE ' table_name ' ADD fulltext (' column ')
5. Adding Multi-column indexes
Mysql>alter TABLE ' table_name ' ADD INDEX index_name (' column1 ', ' column2 ', ' column3 ')
This article briefly describes the application examples of full-text indexing, MySQL demo version 5.5.24.
Q: What is the application of full-text indexing?
A: Full-text indexing is currently the key technology for big data search.
For more detailed introduction please own Baidu, this article no longer elaborated.
--------------------------------------------------------------------------------
 First, how to set?

Click {full-Text search} at the end to set the full-text index, different MySQL version names may be different.

Second, set the conditions
1. The storage engine for the table is MyISAM, and the default storage engine InnoDB does not support full-text indexing (new version MYSQL5.6 InnoDB supports full-text indexing)
2. Field type: char, varchar, and text

Third, the configuration
My.ini the configuration file to add
# mysql Full-text index query keyword minimum length limit
[Mysqld]
Ft_min_word_len = 1
Restart MySQL after saving, execute SQL statement

Copy CodeThe code is as follows:
SHOW VARIABLES

See if the Ft_min_word_len is set up successfully, and if not, make sure
1. Confirm that the My.ini is properly configured, and be careful not to make the wrong My.ini location
2. Verify that MySQL is restarted. Restart your computer
Other related configuration please own Baidu.
Note: After you reset the configuration, indexes that have already been set need to be reset for the build index

Iv. SQL syntax
First generate the Temp table

Copy CodeThe code is as follows:
CREATE TABLE IF not EXISTS ' temp ' (
' id ' int (one) not NULL auto_increment,
' Char ' char (+) is not NULL,
' varchar ' varchar (not NULL),
' Text ' text is not NULL,
PRIMARY KEY (' id '),
Fulltext KEY ' char ' (' char '),
Fulltext KEY ' varchar ' (' varchar '),
Fulltext KEY ' text ' (' text ')
) Engine=myisam DEFAULT Charset=utf8 auto_increment=2;
INSERT into ' temp ' (' id ', ' char ', ' varchar ', ' text ') VALUES
(1, ' A bc I know 1 ', ' A bc I know 1 ', ' A bc I know 1 23 ');

Search ' char ' field ' a ' value

Copy CodeThe code is as follows:
SELECT * from ' temp ' WHERE MATCH (' char ') against (' a ')

But you will find that the query has no results?!
At this point you may think: oops, I clearly follow the steps to do, Ah, is it missing or wrong?
You do not worry, do the procedure is so, mistakes always have, calm down, worry is not solve the problem.

If a keyword in 50% of the data appears, then the word will be used as invalid word.
If you want to remove 50% now please use in BOOLEAN mode to search

Copy CodeThe code is as follows:
SELECT * from ' temp ' WHERE MATCH (' char ') against (' a ' in BOOLEAN MODE)

This makes it possible to query the results, but we do not recommend it.
Full-text index of the search mode of the introduction of self-Baidu.

We're going to add a few useless data. 50% limit is lifted

Copy CodeThe code is as follows:
INSERT into ' temp ' (
' ID ',
' Char ',
' varchar ',
' Text '
)
VALUES (
NULL, ' 7 ', ' 7 ', ' 7 '
), (
NULL, ' 7 ', ' 7 ', ' 7 '
), (
NULL, ' A,BC, I, know, 1,23 ', ' A,BC, I, know, 1,23 ', ' A,BC, I, know, 1,23 '
), (
NULL, ' x ', ' x ', ' x '
);

You can then query the data by executing the following SQL statement

Copy CodeThe code is as follows:
SELECT * from ' temp ' WHERE MATCH (' char ') against (' a ');
SELECT * from ' temp ' WHERE MATCH (' char ') against (' BC ');
SELECT * from ' temp ' WHERE MATCH (' char ') against (' I ');
SELECT * from ' temp ' WHERE MATCH (' char ') against (' know ');
SELECT * from ' temp ' WHERE MATCH (' char ') against (' 1 ');
SELECT * from ' temp ' WHERE MATCH (' char ') against (' 23 ');

The following SQL does not search the data

Copy CodeThe code is as follows:
SELECT * from ' temp ' WHERE MATCH (' char ') against (' B ');
SELECT * from ' temp ' WHERE MATCH (' char ') against (' C ');
SELECT * from ' temp ' WHERE MATCH (' char ') against (' know ');
SELECT * from ' temp ' WHERE MATCH (' char ') against (' Tao ');
SELECT * from ' temp ' WHERE MATCH (' char ') against (' 2 ');
SELECT * from ' temp ' WHERE MATCH (' char ') against (' 3 ');

If you are searching for multiple words, separate them with a space or a comma

Copy CodeThe code is as follows:
SELECT * from ' temp ' WHERE MATCH (' char ') against (' A X ');
SELECT * from ' temp ' WHERE MATCH (' char ') against (' a,x ');

The above SQL can query to three data

Five, participle
See here you should find that the value in our field is also a participle and cannot be inserted directly into the original data.
Full-Text Indexing application process:
1. Receive data-data segmentation-Warehousing
2. Receive data-Data segmentation-Query
Now there is an important question: how to participle data?
Data segmentation generally we will use some mature free word breaker, of course, if you have the ability to do your own word breaker, here we recommend the use of SCWS word-breaker.
Download First
1.php_scws.dll note the corresponding version
2.XDB dictionary File
3. Rule set files


Installing SCWS
1. First build a folder, the location is not limited, but it is best not Chinese path.
2. Extract {ruleset file}, throw all xdb, three INI files to D:\SCWS
3. Copy the Php_scws.dll to the Ext folder in your PHP directory
4. Add the following lines at the end of the php.ini:
[SCWS]

; Note Check that the Extension_dir setting in the php.ini is correct, otherwise set Extension_dir to NULL,
; The Php_scws.dll is then specified as an absolute path.

Extension = Php_scws.dll
Scws.default.charset = UTF8
Scws.default.fpath = "D:\scws"
5. Restart your server
Test

Copy CodeThe code is as follows:
$STR = "Test Chinese word segmentation";
$so = Scws_new ();
$so->send_text ($STR);
$temp = $so->get_result ();
$so->close ();
Var_dump ($temp);

If the installation is unsuccessful, please refer to the official documentation
--------------------------------------------------------------------------------
This allows us to use the full-text indexing technique.

MySQL Build index