DataReader usage: Detailed explanation of comparison and usage conditions of sorting in the database

Assuming that both the MySQL server and the PHP server have been configured in the most suitable way, the system's scalability (Scalability) and user-perceived Performance (User-perceived Performance) are the main goals we pursue. In actual operation, data in MYSQL is often stored in memory in HASH tables, BTREE, etc., and the operation speed is very fast; at the same time, INDEX has already performed some pre-sorting; in many applications, MySQL sorting is the first choice.
　 PHP has the following advantages compared with MySQL:
　 1. Considering the scalability and overall performance of the entire website, sorting in the application layer (PHP) will obviously reduce the load on the database, thus improving the scalability of the entire website. In fact, the cost of sorting in the database is very high, consuming memory and CPU. If there are many concurrent sortings, the DB can easily reach a bottleneck.
　2. If there is a data middle layer between the application layer (PHP) and MYSQL, and it is properly utilized, PHP will have better benefits.
　3. PHP’s in-memory data structure is specially designed for specific applications and is more concise and efficient than databases;
　4. PHP does not need to consider data disaster recovery issues and can reduce this part of the operation loss;
　5. PHP does not exist Table locking problem;
　6. Sorting in MySQL, request and result return also need to be carried out through a network connection, while in PHP you can return directly after sorting, reducing network IO.
　As for the execution speed, the difference should not be huge, unless there is a problem with the application design, causing a lot of unnecessary network IO. In addition, the application layer should pay attention to PHP's Cache settings. If it is exceeded, an internal error will be reported; at this time, it is necessary to evaluate or adjust the Cache according to the application. The specific choice will depend on the specific application.
　List some situations where sorting in PHP is better:
　1. The data source is not in MySQL, there are hard disks, memory or requests from the network;
　2. The data exists in MySQL, the amount is not large, and there is no corresponding index At this time, it is faster to take out the data and sort it in PHP;
　3. The data source comes from multiple MySQL servers. At this time, it is faster to take out the data from multiple MySQL and then sort it in PHP;
　4. In addition to MySQL , there are other data sources, such as hard disk, memory or requests from the network. At this time, it is not suitable to store these data in MySQL and then sort them;
　 List some examples that must be sorted in MySQL:
　 1. Already exist in MySQL This sorted index;
　2. The amount of data in MySQL is large, and the result set requires a very small subset; for example, 1,000,000 rows of data, take the TOP 10;
　3. For the case of one sort and multiple calls, such as In the case of statistical aggregation, which can be provided to different services, sorting in MySQL is preferred. In addition, for deep data mining, the usual approach is to complete complex operations such as sorting at the application layer, and then store the results in MySQL for easy use multiple times.
　4. No matter where the data source comes from, when the amount of data reaches a certain scale, it is no longer suitable for sorting in PHP due to the memory/Cache occupied. At this time, copy, import or store the data in MySQL, and use INDEX to optimize it. is better than PHP. However, it would be better to handle such operations in Java or even C++. For some data, such as aggregation or summary of large data sets, sorting on the client side outweighs the gain and loss. Of course, ideas similar to search engines can also be used to solve similar application situations.
　From the overall consideration of the website, manpower and cost considerations must be included. If the website size and load are small and the manpower is limited (the number of people and capabilities may be limited), sorting at the application layer (PHP) requires a lot of development and debugging work, which is time-consuming and not worth the loss; it is better to process it in DB. Simple and fast. For large-scale websites, electricity and server costs are very high. Careful planning on system architecture can save a lot of costs, which is necessary for the company's sustainable development. At this time, if the application layer (PHP) can be sorted and meet business needs, Try to do it at the application layer.
　That’s all the relevant knowledge about execution sorting in PHP and MySQL execution sorting. I hope this introduction can be helpful to you!

The above has introduced the comparison of sorting in the database using DataReader and a detailed explanation of the conditions for use, including the use of DataReader. I hope it will be helpful to friends who are interested in PHP tutorials.