search
HomeBackend DevelopmentPHP Tutorialtemporary internet files sorting in PHP and sorting in MySQL

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.

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How do you create and use an interface in PHP?How do you create and use an interface in PHP?Apr 30, 2025 pm 03:40 PM

The article explains how to create, implement, and use interfaces in PHP, focusing on their benefits for code organization and maintainability.

What is the difference between crypt() and password_hash()?What is the difference between crypt() and password_hash()?Apr 30, 2025 pm 03:39 PM

The article discusses the differences between crypt() and password_hash() in PHP for password hashing, focusing on their implementation, security, and suitability for modern web applications.

How can you prevent Cross-Site Scripting (XSS) in PHP?How can you prevent Cross-Site Scripting (XSS) in PHP?Apr 30, 2025 pm 03:38 PM

Article discusses preventing Cross-Site Scripting (XSS) in PHP through input validation, output encoding, and using tools like OWASP ESAPI and HTML Purifier.

What is autoloading in PHP?What is autoloading in PHP?Apr 30, 2025 pm 03:37 PM

Autoloading in PHP automatically loads class files when needed, improving performance by reducing memory use and enhancing code organization. Best practices include using PSR-4 and organizing code effectively.

What are PHP streams?What are PHP streams?Apr 30, 2025 pm 03:36 PM

PHP streams unify handling of resources like files, network sockets, and compression formats via a consistent API, abstracting complexity and enhancing code flexibility and efficiency.

What is the maximum size of a file that can be uploaded using PHP ?What is the maximum size of a file that can be uploaded using PHP ?Apr 30, 2025 pm 03:35 PM

The article discusses managing file upload sizes in PHP, focusing on the default limit of 2MB and how to increase it by modifying php.ini settings.

What is Nullable types in PHP ?What is Nullable types in PHP ?Apr 30, 2025 pm 03:34 PM

The article discusses nullable types in PHP, introduced in PHP 7.1, allowing variables or parameters to be either a specified type or null. It highlights benefits like improved readability, type safety, and explicit intent, and explains how to declar

What is the difference between the unset() and unlink() functions ?What is the difference between the unset() and unlink() functions ?Apr 30, 2025 pm 03:33 PM

The article discusses the differences between unset() and unlink() functions in programming, focusing on their purposes and use cases. Unset() removes variables from memory, while unlink() deletes files from the filesystem. Both are crucial for effec

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

MantisBT

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.

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software