Step 4 of optimizing SQL statements in the MySQL database

The above article describes the first three steps for optimizing SQL statements in the MySQL database. Today, we will share with you the fourth step for optimizing SQL statements in the MySQL database, the following describes the specific content of the article. I hope you will gain some benefits after browsing it.

1: When Using indexes, the importance of indexes will not be mentioned, and functions will not be mentioned. first, we need to clarify that all MySQL (the best combination with PHP) indexes (Prima (the most perfect VM management system) ry, unique, index) have storage in the B tree. index terms:

A: Quickly locate record B with the where condition. When the join is executed, search for Row c from other tables to find the max () and min () values for specific index columns.

D: If sorting or grouping is prefixed at the beginning of an available key, sorting or grouping a table

E: a query may be used to optimize the retrieval value of the MySQL database, instead of accessing data files. if the columns in some tables are numeric and exactly the prefix of a column, you can retrieve the values from the index tree for faster speed.

2: The query speed for storing or updating data. grant execution will slightly reduce the efficiency.

MySQL (the best combination with PHP) functions should be highly optimized. You can use benchmarkloop_count, expression) to find out if there is a problem with the query.

Select query speed: If you want to make a select... where... faster. All I can think of is creating an index. you can run myisamchk -- analyze on a table to optimize queries for MySQL databases. you can use myisamchk -- sort-index -- sort-records = 1 to sort an index and data.

3: optimize the where clause for MySQL (the best combination with PHP)

3.1: Remove unnecessary parentheses:

A AND B) AND c OR (a AND B) AND (a AND d)> (a AND B AND c) OR (a AND B AND c AND d)

3.2: Constant

AB> 5 AND B = c AND a = 5

3.3: delete a constant condition

B> = 5 AND B = 5) OR (B = 6 AND 5 = 5) OR (B = 100 AND 2 = 3)> B = 5 OR B = 6

3.4: the constant expression used by the index is calculated only once.

3.5: in a table, no where count (*) directly retrieves information from the table

3.6: All constants are read before any other table in the query.

3.7: The best join combination for external join tables is to try to find all possibilities.
 

3.8: if an order by clause and a different group by clause, order by, or group by clause contain columns not from the first joined table, create a temporary table.

3.9: If SQL _small_result is used, msyql uses a table in the memory.

3.10: indexes of each table are used for queries and indexes that span less than 30% rows.

3.11 skip rows that do not match the having clause before each record is output

4: MySQL database optimization left join

In MySQL (the best combination with PHP), a left join B is implemented as follows:

A: Table B depends on Table.

B: Table a depends on all tables used in the left join condition except B)

C: All left join conditions are moved to the where clause.

D: optimize all connected MySQL databases. Except a table, it is always read after all the tables it depends on. If there is a circular dependency, an error will occur.

E: perform all the standard where optimization f: If a row matches the where clause, but B does not have any matching left join conditions, all rows generated in B that are set to NULL

G: If left join IS used to locate rows that do NOT exist in some tables and column_name is null in the where clause (column_name is not null ). then, after MySQL (the best combination with PHP) finds a row that matches the left join condition, it will stop searching for more rows.

5: MySQL database optimization limit

A: If you use limit to select only one row, when MySQL (the best combination with PHP) needs to scan the entire table, it serves as an index.

B: If the limit # And order by, MySQL (the best combination with PHP) are used, if row # is found, the sorting will end, instead of sorting the entire table.

C: When limit # and distinct are combined, MySQL (the best combination with PHP) will stop if line # is found.

D: As long as MySQL (the best combination with PHP) has sent the first line to the customer, MySQL (the best combination with PHP) will discard the query

E: limit 0 always returns an empty set quickly.

F: Use limit for the temporary table size # calculate the amount of space required to solve the query

6: MySQL database optimization insert

Insert a record consists of the following:

A: Connection 3)

B: Send query to server 2)

C: Analysis query 2)

D: insert record 1 * record size)

E: insert Index 1 * index)

F: Disable 1)

The preceding figure is proportional to the total time.

Some methods to improve the insert speed:

6.1: if many rows are inserted from a connection at the same time, insert with multiple values is faster than using multiple statements.

6.2: if many rows are inserted from different connections, the insert delayed statement is faster.

6.3: myisam is used. If no row is deleted in the table, the row can be inserted While select: s is running.

6.4: when loading a table from a text file, load data infile is usually 20 times faster than insert.
3.6: All constants are read before any other table in the query.

3.7: The best join combination for external join tables is to try to find all possibilities.
 

3.8: if an order by clause and a different group by clause, order by, or group by clause contain columns not from the first joined table, create a temporary table.

3.9: If SQL _small_result is used, msyql uses a table in the memory.

3.10: indexes of each table are used for queries and indexes that span less than 30% rows.

3.11 skip rows that do not match the having clause before each record is output

4: MySQL database optimization left join

In MySQL (the best combination with PHP), a left join B is implemented as follows:

A: Table B depends on Table.

B: Table a depends on all tables used in the left join condition except B)

C: All left join conditions are moved to the where clause.

D: perform all join optimization. Except a table, it is always read after all the tables it depends on. If there is a circular dependency, an error will occur.

E: perform all the standard where optimization f: If a row matches the where clause, but B does not have any matching left join conditions, all rows generated in B that are set to NULL

G: If left join IS used to locate rows that do NOT exist in some tables and column_name is null in the where clause (column_name is not null ). then, after MySQL (the best combination with PHP) finds a row that matches the left join condition, it will stop searching for more rows.

5: MySQL database optimization limit

A: If you use limit to select only one row, when MySQL (the best combination with PHP) needs to scan the entire table, it serves as an index.

B: If the limit # And order by, MySQL (the best combination with PHP) are used, if row # is found, the sorting will end, instead of sorting the entire table.

C: When limit # and distinct are combined, MySQL (the best combination with PHP) will stop if line # is found.

D: As long as MySQL (the best combination with PHP) has sent the first line to the customer, MySQL (the best combination with PHP) will discard the query

E: limit 0 always returns an empty set quickly.

F: Use limit for the temporary table size # calculate the amount of space required to solve the query

6: MySQL database optimization insert

Insert a record consists of the following:

A: Connection 3)

B: Send query to server 2)

C: Analysis query 2)

D: insert record 1 * record size)

E: insert Index 1 * index)

F: Disable 1)

The preceding figure is proportional to the total time.

Some methods to improve the insert speed:

6.1: if many rows are inserted from a connection at the same time, insert with multiple values is faster than using multiple statements.

6.2: if many rows are inserted from different connections, the insert delayed statement is faster.

6.3: myisam is used. If no row is deleted in the table, the row can be inserted While select: s is running.

6.4: when loading a table from a text file, load data infile is usually 20 times faster than insert.

6.5: The table can be locked and inserted. The major speed difference is that after all the insert statements are completed, the index buffer is saved to the hard disk only once. it is generally faster to store data as multiple insert statements. if you can use a single statement to insert all rows, locking is not required. locking also reduces the overall connection time. however, the maximum waiting time for some threads will rise. for example

 
 

  
  
thread 1 does 1000 inserts  
  
  
thread 2,3 and 4 does 1 insert  
  
  
thread 5 does 1000 inserts  
 
 

If no lock is used, 2, 3, 4 will be completed before 1 and 5. if locked, 2, 3, 4, it may be completed after 1 and 5. however, the overall time should be 40% faster. because the insert, update, and delete operations are very fast in MySQL (the best combination with PHP), by locking more than five successive inserts or updates to a row, it will achieve better overall performance.

If you insert many rows, you can create a lock tables, and occasionally create an unlock tables for about every 1000 rows) to allow other threads to access the table. this will still lead to good performance. load data infile is still very fast for loading data.

To speed up load data infile and insert, expand the keyword buffer.

7. Optimize the update speed

The speed depends on the size of the updated data and the number of updated indexes.

Another way to make update faster is to delay modification, and then make a lot of modifications on one row and one row. If the table is locked, make a lot of modifications on one row and one row faster than one at a time.

Optimize the delete speed of the 8MySQL Database

The time for deleting a record is proportional to the number of indexes. to delete the record more quickly, you can increase the index cache size to delete all rows from a table much faster than deleting most of the table.