PHP MySQL A simple example of query optimization

Therefore, we need to do a good job of MySQL query optimization. The following is a simple example that shows how different SQL statements affect query speed:

There is such a table test, which has a self increasing ID as the primary index. To query for a record of the ID number in a range, you can use the following SQL statement:

The code is as follows	Copy Code
SELECT * From ' Test ' ORDER BY ID ASC Limit 208888,50

The meaning of this SQL statement is to take 50 records backwards from the record with ID number 208888. In a 300,000-record database, the time to execute this statement is 40-50 seconds when the primary index has been established.

So are there any faster SQL statements to execute? Obviously there is. Look at the following SQL statement:

The code is as follows	Copy Code
SELECT * From ' Test ' WHERE ID BETWEEN 208838 and 208888

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

The reason is because although the id attribute has been indexed, but the ranking is still a very high cost operation, to be cautious. The second statement allows MySQL to take full advantage of the already established B + Tree index in the database, so it is hundreds of times times faster to find it.

This shows that the Web site developers in the use of SQL statements, must be cautious, because an inadvertent SQL statement, may make your site access to a sharp decline in the background database under great pressure, and quickly fall into the dilemma of unable to open the page