This article was first published on InfoQ Chinese site. Author: Ming Ling (dragon), Fenng. Note: Friends who want to reprint please note the first author of this article!
This article is a summary made by dragon’s friend after he emailed to discuss it. Sorting in the DB or in the application is a very interesting topic. Dragon actually summarized it very well in his first email. I just added a few suggestions. Put it up now and share it with everyone. This article has also been submitted to InfoQ Chinese Station.
Q: List the reasons why performing sorting in PHP is better than sorting in MYSQL? Give some examples where sorting has to be done in MYSQL?
A: Generally speaking, execution efficiency needs to consider the load conditions of CPU, memory and hard disk. Assuming that the MYSQL server and PHP server have been configured in the most suitable way, then the scalability of the system (Scalability) and User-perceived Performance is the main goal 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. Sorting in the application layer (PHP) must also be performed in memory. Compared with MYSQL, it has the following advantages:
- 1. Considering the scalability and overall performance of the entire website, sorting in the application layer (PHP) is obvious It will 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. There is no table locking problem in PHP;
- 6. Sorting in MYSQL, requesting and returning results 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, etc.;
- 2. The data is stored 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 with PHP;
- 3. The data source comes from multiple MYSQL servers. At this time, it is faster to retrieve 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 data that must be sorted in MYSQL Example:
- 1. This sorted index already exists in MYSQL;
- 2. The amount of data in MYSQL is large, and the result set requires a very small subset; for example, if there are 1,000,000 rows of data, take the TOP 10;
- 3. For situations where one sorting and multiple calls are required, such as statistical aggregation, which can be provided to different services, then 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, the data should be copied, imported or stored in MYSQL, and optimized with INDEX , is better than PHP. However, it would be better to handle such operations in Java or even C++. [Some data similar to the 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.
The above introduces the sorting of temporary internet files in PHP and the sorting in MySQL, including the content of temporary internet files. I hope it will be helpful to friends who are interested in PHP tutorials.

Laravel simplifies handling temporary session data using its intuitive flash methods. This is perfect for displaying brief messages, alerts, or notifications within your application. Data persists only for the subsequent request by default: $request-

The PHP Client URL (cURL) extension is a powerful tool for developers, enabling seamless interaction with remote servers and REST APIs. By leveraging libcurl, a well-respected multi-protocol file transfer library, PHP cURL facilitates efficient execution of various network protocols, including HTTP, HTTPS, and FTP. This extension offers granular control over HTTP requests, supports multiple concurrent operations, and provides built-in security features.

Laravel provides concise HTTP response simulation syntax, simplifying HTTP interaction testing. This approach significantly reduces code redundancy while making your test simulation more intuitive. The basic implementation provides a variety of response type shortcuts: use Illuminate\Support\Facades\Http; Http::fake([ 'google.com' => 'Hello World', 'github.com' => ['foo' => 'bar'], 'forge.laravel.com' =>

Do you want to provide real-time, instant solutions to your customers' most pressing problems? Live chat lets you have real-time conversations with customers and resolve their problems instantly. It allows you to provide faster service to your custom

Laravel's service container and service providers are fundamental to its architecture. This article explores service containers, details service provider creation, registration, and demonstrates practical usage with examples. We'll begin with an ove

PHP logging is essential for monitoring and debugging web applications, as well as capturing critical events, errors, and runtime behavior. It provides valuable insights into system performance, helps identify issues, and supports faster troubleshoot

Article discusses late static binding (LSB) in PHP, introduced in PHP 5.3, allowing runtime resolution of static method calls for more flexible inheritance.Main issue: LSB vs. traditional polymorphism; LSB's practical applications and potential perfo

The article discusses adding custom functionality to frameworks, focusing on understanding architecture, identifying extension points, and best practices for integration and debugging.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

SublimeText3 Chinese version
Chinese version, very easy to use

SublimeText3 English version
Recommended: Win version, supports code prompts!

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Dreamweaver CS6
Visual web development tools

WebStorm Mac version
Useful JavaScript development tools