Understanding Query Cost in SQL

Recently, I've been delving into system design, aiming to uncover what happens beneath the surface—how systems operate, where bottlenecks occur, and how we can optimize for speed, reliability, and security.

Every SQL query comes with a performance cost. Some operations are lightweight, while others can significantly slow down your application. Because each database query can directly affect your server's responsiveness, it's crucial to understand what makes certain queries more expensive than others.

In this article, we'll break down the factors that contribute to SQL query costs and explore how you can make smarter choices to keep your systems fast and efficient.

What determines a Query cost?

When you run a SQL query, several factors work together to determine how “expensive” that operation is for your database. Query cost refers to the resources like CPU, memory, disk I/O, and network bandwidth—things that your database engine must use to execute a given command. Understanding these factors can help you write more efficient queries and design better systems.

1. Data Volume: The more data a query has to scan or process, the higher the cost. For example, searching through millions of rows without an index will take much longer than querying a small, well-indexed table.

2. Index Usage: Indexes can dramatically speed up read operations by letting the database find data without scanning every row. However, maintaining indexes also adds overhead to write operations like INSERT, UPDATE, and DELETE.

3. Query Complexity: Complex queries with multiple JOINs, subqueries, aggregations, or sorting operations require more processing power and memory. The more logic the database has to execute, the higher the cost.

4. Locking and Concurrency: When multiple queries try to access or modify the same data simultaneously, the database may need to lock certain rows or tables. This can increase latency and resource usage, especially for write-heavy workloads.

5. Hardware and Configuration: The underlying hardware (CPU, RAM, disk speed) and database configuration (buffer sizes, cache settings) also play a role in how efficiently queries are executed.

6. Network Overhead: If your database and application servers are on different machines, network latency and bandwidth can add to the overall query cost when transferring large result sets.

In short, query cost is a combination of how much data is touched, how the data is accessed, and how complex the query logic is, all influenced by the environment in which the database runs.

Let's look at SQL Commands

SELECT Command

The SELECT command allows you to retrieve data from one or more tables in your database.

What happens under the hood?

When you execute a SELECT query, the database engine parses your request and determines the most efficient way to fetch the data. If your query is well-optimized and uses indexes, the engine can quickly locate the relevant rows. Otherwise, it may need to perform a full table scan, reading every row to find matches—a much more resource-intensive process. The query planner also decides the order of operations, how joins are executed, and whether to use temporary storage for sorting or aggregations.

Expensive patterns:

• Full table scans: Occur when there’s no usable index for the WHERE clause, forcing the database to read every row.
• Complex joins: Joining large tables without proper indexing can lead to massive intermediate result sets and slow performance.
• Subqueries and nested SELECTs: These can multiply the amount of data processed, especially if not correlated efficiently.
• ORDER BY and GROUP BY on large datasets: Sorting and grouping require additional memory and processing, which can become costly at scale.

Examples:

• Suppose you want to find all users with a specific email domain:

SELECT * FROM users WHERE email LIKE '%@example.com';

If there’s no index on the email column, the database must scan every user row to check the condition, making this query expensive for large tables.

Adding an index on the email column can significantly reduce the cost, allowing the database to quickly locate matching rows.

• Now suppose you want to list all orders along with customer names for customers from a specific city:

SELECT orders.id, customers.name, orders.total
FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.city = 'New York';

If there are no indexes on customers.city or orders.customer_id, the database must scan both tables and compare every possible pair to find matches. This results in a costly operation, especially as the tables grow larger. Additionally, if orders and customers are both large, the intermediate result set can consume significant memory and processing time.

Adding an index on customers.city allows the database to quickly filter relevant customers. Similarly, indexing orders.customer_id speeds up the join operation. With proper indexes, the database can efficiently match only the necessary rows, dramatically reducing the query cost.

INSERT Command

The INSERT statement is used to add new rows of data into a table.

What happens under the hood?

When you execute an INSERT, the database engine first checks if the data types and constraints (like NOT NULL, UNIQUE, or FOREIGN KEY) are satisfied. If the table has any indexes, the engine updates them to include the new data. For tables with triggers or default values, those are also processed during the operation. Each insert is logged for durability and potential rollback, which can add overhead, especially in systems with heavy write activity.

Expensive patterns:

• Bulk inserts without batching: Inserting a massive number of rows in a single transaction can overwhelm memory and transaction logs, causing slowdowns or even failures.
• Tables with many indexes: Every index must be updated when new data is inserted, which increases the time and resources required for the operation.
• Constraint checks: Validating foreign keys or unique constraints for each row can become costly, particularly in large or highly related tables.

Examples

• Adding a single new user to the users table:

INSERT INTO users (name, email, city) 
VALUES ('Alice', '[email protected]', 'New York');

This operation is straightforward and typically fast, especially if the table has only a few indexes and constraints.

• Now suppose you want to add multiple orders for a customer, and each order must reference an existing customer in the customers table (enforced by a foreign key constraint):

INSERT INTO orders (customer_id, order_date, total)
VALUES 
  (101, '2025-06-14', 150.00),
  (101, '2025-06-15', 200.00),
  (101, '2025-06-16', 175.00);

Here, the database must check that customer_id 101 exists in the customers table for each row and update any relevant indexes. If the orders table has several indexes or triggers, or if you’re inserting thousands of rows at once, the operation can become much more resource-intensive and slower.

Using transactions and batching can help manage the cost of such complex inserts.

Batching Example:

async function batchInsertOrders(db, orders, batchSize = 1000) {
  for (let i = 0; i < orders.length; i += batchSize) {
    const batch = orders.slice(i, i + batchSize);
    const values = [];
    const params = [];

    batch.forEach((order, idx) => {
      // For each order, push the values and build the parameterized query
      values.push(`($${idx * 3 + 1}, $${idx * 3 + 2}, $${idx * 3 + 3})`);
      params.push(order[0], order[1], order[2]);
    });

    const query = `
      INSERT INTO orders (customer_id, order_date, total)
      VALUES ${values.join(', ')}
    `;

    await db.query('BEGIN');
    await db.query(query, params);
    await db.query('COMMIT');
  }
}

UPDATE Command

The UPDATE statement allows you to modify existing records in a table.

What happens under the hood?

When an UPDATE command runs, the database engine locates the rows that match your WHERE clause, locks them to prevent conflicting changes, checks for constraint violations, applies the new values, and updates any relevant indexes. Each change is logged for durability and possible rollback, which adds overhead, especially with large or complex updates.

Expensive patterns:

• Updating large numbers of rows: Running an UPDATE without a selective WHERE clause can affect every row in the table, leading to significant I/O and locking overhead.
• Updating indexed columns: When you modify a column that’s part of one or more indexes, the database must update those indexes for each changed row, increasing the operation’s cost.
• Complex WHERE conditions: If your WHERE clause uses non-indexed columns or involves complex logic, the database may need to scan the entire table to identify matching rows.
• Cascading updates and triggers: If your table uses foreign keys with cascading updates or has triggers that fire on update, each affected row can cause additional operations, further increasing cost.

Examples

• Suppose you want to update the city for a single user based on their email address:

UPDATE users
SET city = 'San Francisco'
WHERE email = '[email protected]';

If there’s an index on the email column, the database can quickly find and update the relevant row. This is a lightweight operation with minimal impact on performance.

• Now, imagine you need to update the status of all orders placed before a certain date and with a total greater than $500:

UPDATE orders
SET status = 'archived'
WHERE order_date < '2025-01-01'
  AND total > 500;

This query may affect many rows, especially in a large table. If the order_date or total columns aren’t indexed, the database must scan the entire table to find matches, making the operation much more expensive. Additionally, if the status column is indexed, the index must be updated for every changed row, adding further cost.

To make this update more efficient, you can add indexes on the columns used in the WHERE clause—order_date and total. This allows the database to quickly locate only the relevant rows instead of scanning the entire table. For very large tables, consider batching the updates to reduce locking and transaction log pressure.

Tips for optimizing Query Cost

• Use Indexes Wisely: Add indexes to columns that are frequently used in WHERE clauses, JOIN conditions, and ORDER BY statements. However, avoid over-indexing, as each index adds overhead to INSERT, UPDATE, and DELETE operations.

• Write Selective Queries: Always aim to filter data as much as possible in your queries. Use specific WHERE clauses to limit the number of rows processed.

• Avoid SELECT *: Only retrieve the columns you need, rather than selecting all columns from a table. This reduces the amount of data transferred and processed.

• Batch Write Operations: For large INSERT, UPDATE, or DELETE operations, process data in smaller batches to reduce locking, transaction log usage, and potential timeouts.

• Keep Transactions Short: Long-running transactions can hold locks and block other operations. Commit your changes as soon as possible.

• Optimize Joins: Use appropriate join types and make sure joined columns are indexed. Avoid joining large tables without filters or indexes.

• Use Proper Data Types: Choose the most efficient data types for your columns to minimize storage and speed up processing.

Hi, I’m Samit, a software developer and freelancer passionate about building real world projects. If you’re looking to collaborate or just want to say hi, check out my portfolio. Let’s connect!