Phpmysql: A simple example of query optimization _ PHP Tutorial

Phpmysql is a simple example of query optimization. PHP + Mysql is the most frequently used golden partner. they can work together to achieve the best performance. of course, if they are used with Apache, they will be more Perfect. Therefore, we need to make PHP + Mysql a prime partner that is most often used. they can work together to achieve the best performance. of course, if they are used with Apache, they will be more Perfect.

Therefore, you need to optimize the mysql Query. The following is a simple example to show the impact of different SQL statements on the query speed:

There is such a table test, which has an auto-increment id as the primary index. You can use the following SQL statement to query records whose IDs are in a certain range:

The code is as follows:
SELECT * FROM 'test' Order by id asc Limit 208888,50

This SQL statement indicates that 50 records are retrieved from the beginning of the record with the ID number 208888. In a database with 0.3 million records, if the primary index has been set up, the execution time of this statement is 40 ~ 50 seconds.

Is there any faster SQL statement for execution? Obviously. Take a look at the following SQL statement:

The code is as follows:
SELECT * FROM 'test' WHERE id BETWEEN 1, 208838 & 208888

This statement uses a condition for filtering. In practice, the test execution time is about 0.06 seconds.

The reason is that, although the id attribute has been indexed, sorting is still a very costly operation and should be used with caution. The second statement allows MySql to make full use of the B + tree index already created in the database, so the search speed is quite fast, several hundred times that of the original.

It can be seen that website developers must be cautious when using SQL statements. a careless SQL statement may result in a sharp decline in website access speed and huge pressure on the background database, and soon fell into the dilemma of being unable to open the page

Bytes. Therefore, you need to do a good job...