php mysql A simple example of query optimization_PHP tutorial-PHP Tutorial-php.cn

php mysql A simple example of query optimization_PHP tutorial

WBOY

Release： 2016-07-20 11:11:06

Original

948 people have browsed it

PHP+Mysql is the most frequently used golden partner. When used together, they can achieve the best performance. Of course, if used together with Apache, it will be even more perfect.

Therefore, it is necessary to optimize mysql queries. The following is a simple example to show the impact of different SQL statements on query speed:

There is such a table test, which has an auto-incrementing id as the primary index. Now to query the records with ID numbers within a certain range, you can use the following SQL statement:

代码如下	复制代码
SELECT * FROM `test` order by id asc limit 208888,50

This SQL statement means to retrieve 50 records starting from the record with ID number 208888. When tested in a database with 300,000 records, when the primary indexes have been established, the execution time of this statement is 40 to 50 seconds.

So is there any faster SQL statement to execute? Apparently there is. Take a look at the following SQL statement:

代码如下	复制代码
SELECT * FROM `test` WHERE id BETWEEN 208838 AND 208888

This statement uses a condition to filter, and the actual test execution time is about 0.06 seconds.

The reason is that although there is an index on the id attribute, sorting is still a very expensive operation and should be used with caution. The second statement allows MySql to make full use of the B+ tree index that has been established in the database, so the search speed is quite fast, hundreds of times faster than before.

It can be seen that website developers must be careful when using SQL statements, because a careless SQL statement may cause your website access speed to drop sharply, and the back-end database will face huge pressure, and Soon I fell into the dilemma of being unable to open the page