Comparison of sorting in PHP and MySQL Databases and detailed usage conditions

PHPAndMySQLDatabaseSortThe comparison and usage conditions are what we will introduce in this article. Generally, the execution efficiency needs to consider the load of CPU, memory and hard disk, assuming that both the MySQL server and the PHP server have been configured in the most appropriate way, the Scalability of the system and the User-perceived Performance are our main goals. In actual operation, data in MYSQL is often stored in memory in HASH tables, BTREE, and other ways, and the operation speed is very fast. At the same time, indexes have been pre-sorted. In many applications, mySQL sorting is the first choice.

Compared with MySQL, PHP has the following advantages:

1. Considering the scalability and overall performance of the entire website, sorting at the application layer of PHP will significantly reduce the database load, thus improving the scalability of the entire website. In fact, the cost of database sorting is very high, which consumes memory and CPU. If there is a lot of concurrent sorting, it is easy for DB to reach the bottleneck.

2. If there is still a Data Middle Layer Between the application layer (PHP) and MYSQL, which can be used properly, PHP will have better benefits.

3. The data structure of PHP in the memory is specially designed for specific applications, which is more concise and efficient than the database;

4. PHP does not need to consider data disaster recovery issues, which can reduce operation loss;

5. PHP does not have the table lock problem;

6. Sorting in MySQL, requests and returned results still need to be performed through a network connection. After sorting in PHP, you can directly return results, reducing network I/O.

As for the execution speed, there should be no big difference, unless there is a problem with the application design, resulting in a large number of unnecessary network IO. In addition, the application layer should pay attention to the PHP Cache settings. If it exceeds the threshold, an internal error will be reported. In this case, make an evaluation based on the application or adjust the Cache. The specific selection depends on the specific application.

Better sorting in PHP:

1. The data source is not in MySQL, and there are hard disks, memory, or network requests;

2. data is stored in MySQL, with a small amount of data and no corresponding indexes. In this case, the data is retrieved and sorted in PHP faster;

3. Data Sources come from multiple MySQL servers. In this case, data is retrieved from multiple MySQL servers and sorted in PHP faster;

4. In addition to MySQL, there are other data sources, such as hard disk, memory, or network requests. It is not suitable to store the data in MySQL and sort it again;

List some instances that must be sorted in MySQL:

1. the Sorting index already exists in MySQL;

2. The data volume in MySQL is large, and the result set requires a small subset. For example, for the first 1000000 rows of data, TOP 10 is used;

3. for one-time sorting and multiple calls, such as statistical aggregation, which can be used by different services, sorting in MySQL is the first choice. In addition, for deep data mining, complex operations such as sorting are usually performed at the application layer, and the results can be saved to MySQL for multiple use.

4. No matter where the data source comes from, when the data volume reaches a certain scale, it is no longer suitable for sorting in PHP due to the memory/Cache usage; in this case, data is copied, imported, or MySQL exists, and optimized with INDEX, which is better than PHP. However, it is better to use Java or even C ++ to handle such operations. Some data similar to aggregate or aggregate large datasets is not worth the cost of sorting on the client. Of course, similar search engines can also be used to solve similar applications.

From the overall perspective of the Website, you must consider manpower and costs. If the website has a small scale and load, but the limited number of people and capabilities may be limited), at this time, in the application layer PHP) to do a lot of development and debugging work, it takes time, not worth the candle; it is better to process in DB, which is simple and fast. For large-scale websites, the cost of electricity and servers is very high, and the careful calculation of the system architecture can save a lot of cost, which is necessary for the company's Sustainable Development; at this time, if the application layer (PHP) sort and meet business requirements, and try to do so at the application layer.

This article describes how to execute sorting in PHP and execute sorting in MySQL. I hope this article will be useful to you!