MYSQL11---Primary key normal full-text index

1. 1 Primary key index added when a table is set as the primary key, then the column is the key indexCreate Tableaaa (IDintUnsignedPrimary Keyauto_increment, namevarchar( +) not NULLdefaul "); This is the ID column is automatically the primary key index. If you create a table without specifying a primary key index, you can also add, after creating the table, the directive:Alter TableTable nameAdd Primary Key(column name); For example:Create TableBBB (IDint, namevarchar( +) not NULL default");Alter TableBbbAdd Primary Key(ID);

1 . 2 Normal index in general, the creation of a normal index is to create a table first, and then create a normal index such as: Create Table  intvarchar (+)) create index on table (column 1, column name 2);

1. 3 Create full-text index full-text index, mainly for the retrieval of files, text, such as articles, full-text index for MyISAM useful. Create:CREATE TABLEArticles (IDINTUNSIGNED auto_increment not NULL PRIMARY KEY, titleVARCHAR( $), BodyTEXT, Fulltext (title,body) #title和body的复合全文索引) engine=MyISAM charset UTF8;INSERT  intoArticles (Title,body)VALUES     ('MySQL Tutorial','DBMS stands for DataBase ...'),     ('How to use MySQL well','After you went through a ...'),     ('Optimizing MySQL','In this tutorial we'll show ...'),     ('1001 MySQL Tricks','1. Never run mysqld as root. 2 .... .'),     ('MySQL vs. Yoursql','In the following database comparison ...'),     ('MySQL Security','When configured properly, MySQL ...'); How to use full-text indexing:

Error Usage:Select *  fromArticleswhereBody like‘%Mysql%'; ' will not be used to full-text index proof: explainSelect *  fromArticleswhereBody like‘%Mysql%'+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+- ------------+|Id|Select_type| Table    |Partitions|Type|Possible_keys| Key  |Key_len|Ref|Rows|Filtered|Extra|+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+- ------------+|  1 |Simple|Articles| NULL       |  All  | NULL          | NULL | NULL    | NULL |    6 |    16.67 |Usingwhere |+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+- ------------+

the correct usage is:Select *  fromArticleswhereMatch (Title,body) against ('Database’); "Yes, Possible_keys,Key"+----+-------------+----------+------------+----------+---------------+-------+---------+-------+------+----- -----+-------------+|Id|Select_type| Table    |Partitions|Type|Possible_keys| Key   |Key_len|Ref|Rows|Filtered|Extra|+----+-------------+----------+------------+----------+---------------+-------+---------+-------+------+------ ----+-------------+|  1 |Simple|Articles| NULL       |Fulltext|Title|Title| 0       |Const|    1 |       - |Usingwhere |+----+-------------+----------+------------+----------+---------------+-------+---------+-------+------+----- -----+-------------+Mysql> Select *  fromArticleswhereMatch (Title,body) against ('Database');+----+-------------------+------------------------------------------+|Id|Title|Body|+----+-------------------+------------------------------------------+|  5 |MySQL vs. Yoursql| inchThe followingDatabaseComparison ...||  1 |MySQL Tutorial|DBMS Stands for DataBase...|+----+-------------------+------------------------------------------+

? Description:1fulltext indexes in MySQL are only valid for MyISAM2MySQL self-provided fulltext is valid for English----->sphinx (coreseek) Technology specializes in Chinese full-text indexing3. The usage method is match (field name:) against ('Key Words')4. A full-text index is called a stop word, because in a text, creating an index is an infinite number, so for some common words and characters, it is not created (common words'you, me, him .'too much, even if the query comes out also does not have much meaning and the match degree, these words, is called the stop word. It is very difficult to build index in the full text, if each word is built index is very difficult, because the characters are too many, so only for the rare words (keywords) to build index, otherwise each word is indexed, the amount is MySQL> SelectMatch (Title,body) against ('Database') fromarticles;+--------------------------------------------------------+|Match (Title,body) against ('Database')|+--------------------------------------------------------+|                    0.6554583311080933 |(The probability that the first record might match the database is 0.)65545833)|                                     0 ||                                     0 ||                                     0 ||                    0.6626645922660828 ||                                     0 |+--------------------------------------------------------+

Mysql> SelectMatch (Title,body) against ('da') fromarticles;+---------------------------------+|Match (Title,body) against ('da')|+---------------------------------+|                               0 |(DA is not indexed)|                               0 ||                               0 ||                               0 ||                               0 ||                               0 |+---------------------------------+

MYSQL11---Primary key normal full-text index