1. Search Method
Full-text index searchType = MyISAM; Fulltext key 'name' (column1, column2) # Find the word "no"
Mysql> select * from T3 where match (name, name2)
-> Against ('won't Do It 'in Boolean mode); # search for any words starting with the word "won't do it"
Mysql> select * from T3 where match (name, name2)
-> Against ('No, *' in Boolean mode); # search for any words starting with "no" and remove the words "true"
Mysql> select * from T3 where match (name, name2)
-> Against ('+ NO. *-true' in Boolean mode );
Regular Expression search# Search for the word "no"
Mysql> select * from T3 where name2 Regexp "no"; # search for records starting with the word "no"
Mysql> select * from T3 where name2 Regexp "^ no"; # search for the end with the word "no"
Mysql> select * from T3 where name2 Regexp "no right $ ";
2. Improve search efficiency-Index
Index category:Normal index unique // unique index, which is the primary key type | Fulltext // full-text index | spatia // Single-Column index and multi-column Index
Indexing skillsThe index is used in the following situations: to quickly find the rows matching a where clause.
When the join operation is performed, the row is retrieved from other tables.
Find the max () or min () value for a specific index column.
If sorting or grouping is performed on the leftmost prefix of an available key (for example, order by key_part_1, key_part_2), sort or group a table. If all key values follow DESC, the key is read in reverse order.
In some cases, a query can be optimized to retrieve values without consulting data files. If all columns used for some tables are numeric and constitute the leftmost prefix of some keys, the values can be retrieved from the index tree for faster speed. Therefore, all query statements composed of where must be indexed. eg: mysql> select * From tbl_name where col1 = val1 and col2 = val2;
Index creation Syntax:Create [unique | Fulltext | spatial] index @ var {index_name} [@ var {index_type}] on @ var {tbl_name} (@ var {index_col_name },...) @ var {index_col_name }:@ var {col_name} [(@ var {length})] [ASC | DESC] eg: Create index index_name1 on tbl_name (col1, col2 );
Index Efficiency AnalysisMySQL provides a built-in SQL command to help us complete this task. This is the explain command. The general syntax of the explain command is: Explain <SQL command>.
Others: A table can have up to 16 indexes. The maximum index length is 256 bytes.
