Comparison of sorting in database and detailed usage conditions

PHP and MySQL database sorting and use conditions are the main content we want to introduce, usually, the implementation efficiency needs to consider the CPU, memory and hard disk load situation, assuming that the MySQL server and the PHP server are in the most appropriate way to configure, then the system's scalability ( scalability) and user perceived performance (user-perceived performance) are the main goals we pursue. In the actual operation, the data in MYSQL is often in the way of HASH tables, btree, etc. storage in memory, the operation speed is very fast; at the same time, index has been a number of predefined; In many applications, MySQL sorting is preferred.

PHP has the following advantages over MySQL:

1. Considering the scalability and overall performance of the entire website, sorting in the application layer (PHP) will obviously reduce the load of the database, thus enhancing the expansion ability of the whole website. and the database sorting, in fact, the cost is very high, consuming memory, CPU, if the concurrency of a lot of, DB is easy to the bottleneck.

2, if the Application layer (PHP) and MySQL between the data in the middle tier, reasonable use, PHP will have better benefits.

3, PHP in memory data structure specifically for the specific application to design, more concise than the database, efficient;

4, PHP does not consider the data disaster recovery problem, you can reduce this part of the operation loss;

5, PHP does not exist the problem of locking the table;

6, MySQL sorting, request and result return also need to go through the network connection, and PHP can be sorted directly after the return, reducing the network IO.

As for execution speed, the difference should not be very large, unless the application design is problematic, resulting in a large number of unnecessary network IO. In addition, the application layer should be aware of the PHP Cache settings, if the excess will report internal errors, at this time to evaluate according to the application, or adjust the cache. The specific choice will depend on the specific application.

List some of the better things to do in PHP:

1, the data source is not in MySQL, there are hard drives, memory or requests from the network;

2, the data exists in MySQL, the amount is small, and there is no corresponding index, at this time the data out to use PHP to sort faster;

3, the data source from a number of MySQL server, at this time from a number of MySQL to remove data, and then in PHP to sort faster;

4, in addition to MySQL, there are other data sources, such as hard disk, memory or requests from the network, etc., at this time is not suitable for this data into MySQL after sorting;

List some instances that must be sorted in MySQL:

1, MySQL already exists in this sort of index;

2, MySQL data volume is large, and the result set requires a small subset of them, such as 1000000 rows of data, take top 10;

3, for a sorting, multiple invocation of the situation, such as statistical aggregation of the situation can be provided to different services to use, then the ranking in MySQL is preferred. In addition, for the depth of data mining, the common practice is to finish sorting in the application layer and other complex operations, the results are stored in MySQL can be used for many times.

4, no matter where the data source from, when the amount of data to a certain scale, due to occupy memory/cache relationship, no longer suitable for the sort of PHP; At this time the data copy, import or exist MySQL, and index optimization, is superior to PHP. However, it would be better to use Java, or even C + +, to handle such operations. Some data that is similar to large data aggregation or aggregation is not worth the gains in the client order. Of course, there are similar search engine ideas to solve similar applications.

From the overall consideration of the site, it is necessary to add human and cost considerations. If the site size and load is small, and manpower Limited (the number and ability may be limited), at this time in the application layer (PHP) do a lot of the development and debugging work, time-consuming, not worth it, as in db processing, simple and fast. For large-scale web sites, electricity, server costs are very high, in the system structure of the budget, can save a lot of costs, is the company's sustainable development is necessary; At this time, if the application layer (PHP) can be sorted and meet business needs, as far as possible in the application layer.

About PHP in the execution of sorting and the implementation of the sort of MySQL in the relevant knowledge is introduced here, I hope this introduction can be harvested for you!