Simple Example of php + mysql Query Optimization

Simple Example of php + mysql Query Optimization

This article mainly introduces a simple example of php + mysql query optimization, analyzes the SQL statement optimization query skills in php + mysql programming, and provides some reference value for improving the query efficiency, for more information, see

 

 

This article analyzes how to optimize queries in php + mysql. Share it with you for your reference. The specific analysis is as follows:

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, it is necessary to optimize the mysql query. The following is a simple example to show the impact of different SQL statements on the query speed.

A table named test has an auto-incremental id as the primary index. to query records with an id within a certain range, you can use the following SQL statement:

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 the ID number 0.3 million, if the primary index has been set up, the execution time of this statement is 40 ~ 50 seconds, is there any faster SQL statement to execute? Obviously, let's 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 already has an index, sorting is still a very costly operation and should be used with caution. The second statement, mySql can make full use of the B + tree index established in the database, so the search speed is quite fast, hundreds of 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.

I hope this article will help you design the php + mysql program.