How to speed up MySQL Databases

1. starting from the database structure 1. when defining field types, follow the following rules: 1. the minimum length of the selected field is 2. the fixed length type is preferred. define "not null" 4. avoid using "zerofill" 5. if the data to be stored is a string with known and limited values, use Enum or set 2 first. index optimization is crucial (unless otherwise specified, queries are intensive. the smaller the length of the indexed field, the higher the efficiency of the index. the less repeated the value in the indexed field, the higher the efficiency of the index. if the "Group" clause is used in the query statement, create a multi-field index based on the order in which the fields appear. 4. if "distinct" is used in the query statement, create a multi-field index based on the order in which the fields appear. when the "and" condition for multiple different fields in the same table appears in the "where" clause, create a multi-field index based on the order in which the fields appear. 6. when the "or" condition for multiple different fields in the same table appears in the "where" clause, a single field index is created for the fields with the least repeated values. 7. create an index for the "connection field" When querying "internal/external connections. it is meaningless for the "unique" index of "primary key". Do not use 9. use the "not null" attribute 10 as much as possible for the indexed field. for write-intensive tables, minimize indexes, especially "Multi-field indexes" and "unique" indexes 2. optimization of query statements 1. use "Explain" to query index usage, so as to find out the best query statement Writing Method and index setting solution 2. use "select *" with caution. Only required fields 3 are selected during query. when an index is used for a query, the fewer index entries that are traversed, the smaller the index field length, the higher the query efficiency (you can use "Explain" to query the index usage) 4. avoid using the MySQL function to process the query results and send the processing to the client. Program Responsible 5. when using "Limit", try to make the part of the "Limit" part in the front of the entire result set, so that the query speed is faster and the system resource overhead is lower. 6. when the "and" condition of multiple fields is used in the "where" clause, the order in which each field appears must be consistent with that in the Multi-field index. 7. when "like" is used in the "where" clause, the index is used only when the wildcard does not appear in the leftmost end of the condition. in MySQL 4.1 or later versions, avoid using subqueries and try to use "internal/external connections" to achieve this function 9. reduce the use of functions. If possible, use a simple expression instead of 10. avoid performing "or" condition queries on different fields in the "where" clause, and splitting the query statement into multiple single fields is more efficient. Appendix: MySQL Field Length description table MySQL Field Length description tinyint1 byte smallint2 byte mediumint3 byte INTEGER: 4 byte bigint8 byte double8 byte float (x) x <= 24: 4 byte x> 24: 8-byte decimal (M, d) MM> = D: M byte date3 byte datetime8 byte timestamp4 byte time4 byte year1 byte char (m) M byte varchar (m) value Length + 1 byte tinyblob value Length + 1 byte tinytext value Length + 1 byte blob value Length + 2 byte text value Length + 2 byte mediumblob value Length + 3 byte mediumtext value Length + 3 length of the byte longblob value + Length of the 4-byte longtext value + 4-byte enum1 or 2 bytes depends on the number of enumerated values set1, 2, 3, 4, 8 depends on the number of members