MySQL Focus--correct use

1. Some error conditions

When you add an index to a database table, it does make the query take off, but the premise must be the correct query using the index, and if used in an incorrect way, even indexing will not work.
The index does not take effect even if the index is established:

1 -like '%xx '2 SELECT * from TB1 where name is like '%CN ';3 -Using Functions4 SELECT * from tb1 where reverse (name) = ' Wupeiqi ';5 -or6 SELECT * from tb1 where nid = 1 or email = ' [email protected] ';7 Special: When the OR condition has an unindexed columns failure, the following index8 SELECT * from tb1 where nid = 1 or name = ' seven ';9 SELECT * from tb1 where nid = 1 or email = ' [email protected] ' and name = ' Alex 'Ten -Inconsistent type One If the column is a string type, the incoming condition must be enclosed in quotation marks, otherwise ... A SELECT * from tb1 where name = 999; - - != - SELECT * from TB1 where name! = ' Alex ' the Special: If it is a primary key, the index will still go - SELECT * from TB1 where nid! = 123 - -> - SELECT * from TB1 where name > ' Alex ' + Special: If the primary key or index is an integer type, then the index is still gone - SELECT * from tb1 where nid > 123 + SELECT * from tb1 where num > 123 A -Order by at Select email from tb1 order BY name Desc; - when sorting by index, the selected mapping is not indexed if it is not . - Special: If the primary key is sorted, then the index is still gone: - SELECT * from tb1 ORDER by nid desc; -   - -Combined index leftmost prefix in If the combined index is: (name,email) - name and email--Using the index to name--Using the index +Email-Don't use index

2. Other precautions

- 避免使用select*

-  count (1)或 count (列) 代替  count (*) - 创建表时尽量时  char 代替  varchar - 表的字段顺序固定长度的字段优先 - 组合索引代替多个单列索引（经常使用多个条件查询时） - 尽量使用短索引 - 使用连接（ JOIN ）来代替子查询(Sub-Queries) - 连表时注意条件类型需一致 - 索引散列值（重复少）不适合建索引，例：性别不适合3.limit pagination just make a simple statement here. When using select STH from table_name limit 0, 10; The process is found when the amount of data is large. For example, limit 24322,10 needs all to traverse to 20,000 before it gets the required data. It takes a very long time to optimize to: SELECT * from Bigdata where nid > 3000 limit, 10; This will make a range query. Very fast, only to record the last time you queried the NID. Also in the direct input pages such as customer input 4989, how to deal with? It might be possible to use the B-tree array to roughly position the pages first.

MySQL Focus--correct use