Simple example of php mysql query optimization_PHP tutorial

A simple example of php mysql query optimization

This article mainly introduces a simple example of php mysql query optimization, and analyzes the skills of SQL statement optimization query in php mysql programming. Improving query efficiency has certain reference value. Friends in need can refer to it

This article analyzes the method of php mysql query optimization through examples. Share it with everyone for your reference. The specific analysis is as follows:

PHP Mysql is the most commonly 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-incremented id as the main index. Now to query the records whose id numbers are 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
The meaning of this SQL statement is to fetch 50 records starting from the record with ID number 208888, and test it in a database with 300,000 records. When the main index has been established, the time it takes to execute this statement It is 40 to 50 seconds. Is there any faster SQL statement to execute? Obviously there is, look at the following SQL statement:

The code is as follows:

SELECT *
FROM `test`
WHERE id
BETWEEN 208838
AND 208888
This statement uses a condition to filter, and the execution time of the actual test is about 0.06 seconds.

The reason is that although there is already 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 information already created in the database. B-tree index, so the search speed is quite fast, hundreds of times faster than the original.

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, put the backend database under tremendous pressure, and quickly fall into a trap. The dilemma of being unable to open the page.

I hope this article will be helpful to everyone’s php mysql program design.

http://www.bkjia.com/PHPjc/971925.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/971925.htmlTechArticleA simple example of php mysql query optimization This article mainly introduces a simple example of php mysql query optimization and analyzes the php mysql program Tips on SQL statement optimization query in design, for improving query...