---
title: "SELECT * is Bad ..."
description: "I've read many posts about why SELECT * is bad and should be avoided, but most of them don't explain exactly why."
canonical_url: "https://otabek.io/blogs/select-is-bad"
md_url: "https://otabek.io/blogs/select-is-bad.md"
language: "en"
last_updated: "2024-03-20"
tags: ["Database"]
---

# SELECT * is Bad ...

![Cover image](https://telegra.ph/file/05181ffb6134e0c2e3014.jpg)

I've read many posts discussing why **SELECT \*** is bad and should be avoided, but most of them don’t clearly explain why. Today, let's discuss why using **SELECT \*** is not recommended, especially when working with large datasets that include millions of rows or columns.

### 1. Unnecessary I/O

You don't always need to select all columns. If you fetch all columns but only use a few of them, it wastes resources because you're reading unnecessary data from every page (we'll discuss pages in future posts). Also, **SELECT \*** can interfere with **index-only scans**, which are more efficient.

### 2. Increased Network Traffic

If you are fetching unnecessary columns, it means you are consuming more network bandwidth. The more data you transfer, the greater the network load, which can slow down your application, especially when dealing with large datasets.

### 3. Increased Application Memory Usage

As your dataset grows, your application will require more memory to store the unnecessary data being fetched. This can put extra pressure on your application's memory, leading to performance issues.

### 4. Client-Side Deserialization

Before data is sent to the client, it is serialized according to protocol rules. The more data you send, the more **serialization** operations are needed, which consumes CPU resources. After sending, when the client receives the serialized data, it needs to **deserialize** it. This step also consumes CPU resources, adding even more overhead.

### Conclusion

This post doesn't claim that **SELECT \*** is inherently bad, but rather, that not knowing when and where to use it is the real issue. It's fine to experiment with it while learning, but in production environments, using **SELECT \*** indiscriminately can be costly in terms of performance.

Telegram: [otabekswe](https://t.me/otabekswe 'otabekswe')

---

```quiz
{
  "quiz": {
    "id": "select-star-quiz",
    "title": "SELECT * Quiz",
    "description": "Test your understanding of why SELECT * can be problematic",
    "questions": [
      {
        "id": "q1",
        "type": "multiple-choice",
        "question": "What are the problems with using SELECT *? (Select all that apply)",
        "options": [
          { "id": "a", "text": "Unnecessary I/O operations", "description": "" },
          { "id": "b", "text": "Increased network traffic", "description": "" },
          { "id": "c", "text": "It always causes errors", "description": "SELECT * works fine, it's just inefficient." },
          { "id": "d", "text": "Increased application memory usage", "description": "" }
        ]
      },
      {
        "id": "q2",
        "type": "single-choice",
        "question": "What can SELECT * interfere with?",
        "options": [
          { "id": "a", "text": "Index-only scans", "description": "" },
          { "id": "b", "text": "Table creation", "description": "SELECT * doesn't affect table creation." },
          { "id": "c", "text": "Database connections", "description": "SELECT * doesn't affect connections, just query efficiency." },
          { "id": "d", "text": "User authentication", "description": "SELECT * is about query efficiency, not authentication." }
        ]
      },
      {
        "id": "q3",
        "type": "single-choice",
        "question": "What happens to data before it's sent to the client?",
        "options": [
          { "id": "a", "text": "It's serialized", "description": "" },
          { "id": "b", "text": "It's deleted", "description": "Data is serialized for transmission, not deleted." },
          { "id": "c", "text": "It's copied to backup", "description": "Data is serialized, not backed up during queries." },
          { "id": "d", "text": "Nothing happens", "description": "Data must be serialized before network transmission." }
        ]
      },
      {
        "id": "q4",
        "type": "single-choice",
        "question": "When is it okay to use SELECT *?",
        "options": [
          { "id": "a", "text": "Never, it's always bad", "description": "SELECT * is fine for learning and testing." },
          { "id": "b", "text": "While learning and experimenting", "description": "" },
          { "id": "c", "text": "Always in production", "description": "In production, be specific about which columns you need." },
          { "id": "d", "text": "Only with small tables", "description": "Table size matters, but the main point is knowing when to use it." }
        ]
      },
      {
        "id": "q5",
        "type": "drag-drop",
        "question": "Order these from most to least efficient for fetching 2 columns from a 20-column table:",
        "items": [
          { "id": "specific", "content": "SELECT col1, col2 FROM table" },
          { "id": "star", "content": "SELECT * FROM table" }
        ]
      },
      {
        "id": "q6",
        "type": "single-choice",
        "question": "What consumes CPU resources on the client side?",
        "options": [
          { "id": "a", "text": "Deserialization of received data", "description": "" },
          { "id": "b", "text": "Writing the SQL query", "description": "Writing queries doesn't consume much CPU." },
          { "id": "c", "text": "Connecting to the database", "description": "Connection has overhead but deserializing data is the CPU cost." },
          { "id": "d", "text": "Closing the connection", "description": "Closing connections is minimal compared to deserializing data." }
        ]
      }
    ]
  },
  "answers": {
    "q1": { "correctOptionIds": ["a", "b", "d"] },
    "q2": { "correctOptionIds": ["a"] },
    "q3": { "correctOptionIds": ["a"] },
    "q4": { "correctOptionIds": ["b"] },
    "q5": { "correctOrder": ["specific", "star"] },
    "q6": { "correctOptionIds": ["a"] }
  }
}
```


## Sitemap

See the full [Markdown sitemap](/sitemap.md) for all pages.