Optimization when building a table:
- 1. Use smaller data types whenever possible to meet your needs: for example, using mediumint instead of int
- 2, try to set all the columns to NOT NULL, if you want to save null, manually set it, rather than set it as the default value.
- 3. Use varchar, TEXT, blob type as little as possible
- 4. If your data is only a few of the few you know. It is best to use the enum type
- 5. Build the index.
Optimization of query statements:
Writing principles:
- For any SQL statement, be sure to consider its performance.
- Avoid the use of complex SQL statements
- Avoid unnecessary sorting
- Using aliases for tables
- SELECT statement avoids using *
Specific details of the rules:
- The Where condition has no reference function to the left of the equals sign
- In the Where condition for different data types, the display transformation is done.
- Using the NOT exists instead of the in,exists substitution in,exists statement can effectively utilize the index.
- Use >=, <=, to avoid using the not command
- Use union to replace or
SQL optimization Rules