What is the performance impact of using SELECT *?
Jun 19, 2025 am 12:58 AMUsing SELECT * will affect database performance and the required columns should be specified explicitly. First, it increases unnecessary data transmission and network load, especially when the table contains a large number of fields (such as TEXT or BLOB); second, it may cause index failure, trigger additional disk I/O operations, and reduce query efficiency; finally, if the table structure changes, SELECT * may cause application errors or unpredictable behavior, reducing maintainability.
Using SELECT *
can have a noticeable impact on database performance, especially in larger systems or under heavy load. It may seem harmless at first, but it often leads to inefficiencies that add up over time.
Increased Data Transfer and Network Load
Fetching all columns means pulling more data than necessary across the network. If your application only needs a couple of fields (like id
and name
), getting every column from the table—especially if some are large like TEXT
or BLOB
—can significantly increase the amount of data being transferred. This slows down query response times and uses more bandwidth.
For example:
- A table has 15 columns, and one of them is a
TEXT
field storing long descriptions. - Your app only needs two columns for display.
- Using
SELECT *
pulls all 15 columns, including the heavyTEXT
data, even though it's never used.
This extra data adds overhead not just on the network, but also in memory usage and processing time on both the server and client sides.
Index Inefficiency and Extra Disk I/O
When you use SELECT *
, the database might not be able to serve the query using only an index (known as an index-only scan ). Instead, it has to go back to the actual table data to retrieve all columns, which increases disk I/O and slows things down.
Here's what typically happens:
- The query planner sees that not all requested data is in the index.
- It performs a lookup in the main table for each row to get the missing columns.
- This results in slower execution and higher resource consumption.
By explicitly selecting only the needed columns, you increase the chances that the query can be satisfied entirely from an index, which is much faster.
Application Maintainability and Unexpected Behavior
Using SELECT *
can cause issues when the table structure changes. For instance:
- If someone adds a new column to the table, your application might start receiving unexpected data.
- If the code expects a fixed number of columns or specific types, this can lead to bugs or crashes.
Also, ORM tools and other frameworks may behave unpredictably when the schema changes without notice. Explicitly listing columns make your queries more predictable and easier to debug.
In short, while SELECT *
might save a few seconds when writing a query, it can cost much more in performance, scalability, and maintenanceability over time.
The above is the detailed content of What is the performance impact of using SELECT *?. For more information, please follow other related articles on the PHP Chinese website!

Hot AI Tools

Undress AI Tool
Undress images for free

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

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

Clothoff.io
AI clothes remover

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

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Memory frequency is one of the important parameters of computer memory. It refers to the frequency of data transmission speed of the memory module. When choosing memory, we often pay attention to the memory frequency, because the memory frequency directly affects the performance of the computer. This article explores the impact of memory frequency on computer performance. First, an increase in memory frequency can increase the computer's data transfer speed. Memory is where the computer stores data, and when the computer is running tasks, it needs to constantly read and write data. The higher the memory frequency, the faster the data transfer speed.

PHP is a commonly used server-side scripting language that is widely used in the field of web development. In PHP development, various error types are often encountered, such as syntax errors, runtime errors, and logic errors. These error types will have varying degrees of impact on PHP application performance. In order to maintain good application performance, developers need to understand the impact of PHP error types on performance and optimize them. This article will introduce the types of PHP error types and their impact on performance, and give optimization suggestions. 1. PHP error types 1. Language

The impact of process priority on Linux system performance In the Linux operating system, process scheduling is a very important issue, and the priority of the process is one of the key factors affecting process scheduling. In Linux systems, processes can be divided into real-time processes and ordinary processes, and the priority of a process is an important parameter that determines how the system scheduler arranges process execution. The priority of a process is represented by a numerical value, generally ranging from -20 (highest priority) to 19 (lowest priority). The smaller the value, the higher the priority of the process.

Description of the impact of PHPSession cross-domain data volume on performance: Cross-domain refers to data transmission between different domain names or sub-domain names. In web development, PHP's Session is a mechanism used to store user-related information on the server side. However, when the amount of Session data is particularly large and needs to be transmitted under different domain names, it will have a certain impact on performance. This article will use specific code examples to analyze the impact of cross-domain data size on performance. Usage scenario: Suppose we have two domain names:

PhpStudy port occupancy will have a performance impact. 1. Occupying port 80 may cause other applications to be unable to start, affecting the development process. 2. Inadequate service processing capabilities may lead to response delays. 3. Adjusting the configuration and increasing the number of concurrent connections can optimize performance, but sometimes you need to consider replacing the server or migrating to the cloud.

Vue is a popular front-end framework that provides data binding and responsive mechanisms, allowing developers to easily build interactive single-page applications. However, Vue's data listening mechanism will have a certain impact on application performance. This article will explore the impact of data monitoring in Vue on application performance and provide some optimization methods. Vue's data monitoring is implemented by using the Object.defineProperty() method. In Vue, all data is converted into getters and sets

Optimization strategies for data changes and data consistency of PHP and MySQL indexes and their impact on performance Introduction In web development, PHP and MySQL are one of the most commonly used combinations. For the addition, deletion, modification and query operations of large amounts of data, index design and optimization are very important. This article will introduce optimization strategies for data changes and data consistency in PHP and MySQL indexes, explore the impact of these strategies on system performance, and provide corresponding code examples. 1. Index design and maintenance. Determine the index fields. When designing the index, we need

Misuse of inline functions can have negative performance impacts, including code bloat (increased code size and complexity) and performance degradation (increased number of instruction cache misses). To avoid abuse, best practices include inlining only performance-critical functions, being mindful of code size, and considering the compiler's automatic optimization features.
