Simple optimization of MySQL

Reasonable index-building 's recommendations:

(1) Smaller data types are generally better: smaller data types typically require less space in disk, memory, and CPU caches, and are processed faster.

(2) Simple data types are better: integer data is less expensive to handle than characters, because string comparisons are more complex. In MySQL, you should use a built-in date and time data type instead of a string to store the time, and an integer data type to store the IP address.

(3) Try to avoid null: The column should be specified as NOT NULL unless you want to store null. In MySQL, columns with null values are difficult to query optimization because they complicate indexing, index statistics, and comparison operations. You should use 0, a special value, or an empty string instead of a null value.

This section is a few trivial suggestions and points for attention when it comes to indexing and writing SQL statements.

1. use LIMIT 1 When the result set has only one row of data

2. Avoid SELECT *, always specify the columns you need

The more data is read from the table, the more slowly the query becomes. He increases the time it takes for the disk to operate, or if the database server is separate from the Web server. You will experience a very long network delay, simply because the data is not required to be transferred between servers.

3. Use Connection (join) instead of subquery (sub-queries)

Connect (Join): It is more efficient because MySQL does not need to create a temporary table in memory to complete this logical two-step query effort.

4. using ENUM,CHAR instead of VARCHAR, use reasonable field property length

5. use not NULL where possible

6. fixed-length tables are faster

7. splitting a large DELETE or INSERT statement

8. The smaller the query column, the faster

Where condition

In the query, the Where condition is also a relatively important factor, as little as possible and is reasonable where condition is very important, as far as possible in multiple conditions, will extract as little data as possible in front of the conditions, reduce the next where the query time of the condition.

Some where conditions cause the index to be invalid:

Øwhere clause in the query conditions have! =,mysql will not be able to use the index.

Øwhere clause when using the MySQL function, the index will be invalid, for example: SELECT * from TB where left (name, 4) = ' xxx '

Ø when searching for matches using like, the index is valid: SELECT * from TBL1 where name as ' xxx% ', and the index is invalid if '%xxx% '

Simple optimization of MySQL