Start from the database structure
Follow the following rules when defined by field types:
Select field length Minimum Priority Use the Delivery Differential Differential Definition "Not Null" to avoid using "Zerofill" if you want to store, if you want to store, you are known and limited, preferred to use ENUM or SET index Optimization is critical (there is no special instructions if there is no special instructions)
The smaller the length of the indexed field, the higher the efficiency of the index, the less the value of the value, the higher the value of the index, the higher the efficiency of the query, if "group" clause is used, according to the field appears In the order of order, the multi-field index query statement is created. If "Distinct" is used, the multi-field index "WHERE" clause is created according to the order of the fields, and "and" conditions in the same table have multiple different fields. When the multi-field index "WHERE" clause is established in the order of the field, the "or" condition of multiple different fields in the same table has created a single field index to the field index in the field index. When the "Connection Field" index is meaningless to the "connect field" index, do not use the "Not Null" property to use the "Not Null" property as much as possible using the index field to minimize the index, Especially the optimization of "multi-field index" and "unique" index query statement
A lot of "explain" query index usage, in order to find the best query statement writing and index settings to use "select *", when the query is selected, the number of indexes traversed, the less the number of indexes traversed The smaller the length of the index field, the higher the query efficiency (you can use the "Explain" query index usage) to avoid processing the query results using the mysql function, handhaled these processing to the client program is responsible for using "limit", try to make "as possible" The part of the limit is located at the front of the entire result set. This kind of query speed is faster, and the system resource overhead is lower in the "and" "and" condition of "WHERE" clauses. When using "Like" in the "WHERE" clause in the multi-definition index, only the index is used in Mysql 4.1 or later when the wildcard does not appear at the leftmost end of the condition, avoid using subqueries, try to use "Internal / Outer Connection" implements this function to reduce the use of functions, if possible, try to use a simple expression in place in the "WHERE" clause, to split it into the "or" condition query for different fields. The query statement efficiency is higher in multiple single fields
Attached: Mysql field length
