SQL makes it possible for applications to store, retrieve, and analyze large amounts of information, but a query that works correctly is not necessarily a query that works efficiently. As a database grows, poorly written SQL can consume more CPU, memory, storage, and network resources. A query that seems fast during development can become a serious performance problem when thousands or millions of records are involved.
Slow database queries can affect websites, mobile applications, backend services, dashboards, and business systems. Sometimes the cause is a missing index, while other problems come from retrieving unnecessary data, joining tables inefficiently, or forcing the database to perform expensive operations.
Understanding common SQL mistakes can help developers write queries that remain efficient as applications grow.
Selecting Every Column When You Need Only a Few
One of the easiest SQL mistakes is using SELECT * for every query.
For example:
SELECT *
FROM customers;
This query asks the database to return every column for every matching record.
The problem becomes more noticeable when a table contains many columns or large fields such as documents or other sizable data. The database may need to read and transfer information that the application does not actually use.
A better approach is to request only the required columns:
SELECT name, email
FROM customers;
This makes the query’s purpose clearer and can reduce unnecessary data processing and network transfer.
Forgetting to Use Appropriate Indexes
Indexes are among the most important tools for database performance.
Without an appropriate index, a database may need to inspect a large portion of a table to find matching records.
Suppose an application frequently searches users by email:
SELECT id, name
FROM users
WHERE email = ‘user@example.com’;
If the database has no suitable index on email, the query may become increasingly expensive as the table grows.
An appropriate index can help the database locate matching values more efficiently.
However, indexes should not be added blindly. They consume storage and can add work to insert and update operations. The goal is to index columns that support important query patterns.
Using Functions on Indexed Columns
Applying a function to a column inside a filter can sometimes prevent efficient use of an index, depending on the database system and query plan.
For example:
SELECT *
FROM orders
WHERE YEAR(order_date) = 2026;
This asks the database to calculate the year for each candidate row.
In many situations, it may be more efficient to express the condition as a date range:
SELECT *
FROM orders
WHERE order_date >= ‘2026-01-01’
AND order_date < ‘2027-01-01’;
The exact optimization depends on the database engine, indexes, data distribution, and query planner, but the general lesson is important: write filters in ways that give the database a chance to use available indexes effectively.
Writing Leading-Wildcard Searches
Pattern matching with LIKE can be useful, but certain patterns are expensive.
Consider:
SELECT *
FROM products
WHERE name LIKE ‘%phone%’;
When the search begins with %, a normal B-tree index may not be able to efficiently narrow the search from the beginning of the string.
A prefix search such as:
WHERE name LIKE ‘phone%’
can be easier to optimize with suitable indexing.
For applications that require flexible text search across large datasets, specialized search solutions may be more appropriate than repeatedly relying on wildcard SQL queries.
Returning Too Many Rows
Even a well-indexed query can become expensive if it returns an unnecessarily large result set.
For example:
SELECT id, name
FROM products;
If a page needs to display only 20 products, returning hundreds of thousands of records is wasteful.
Use appropriate filtering and pagination.
For instance:
SELECT id, name
FROM products
ORDER BY id
LIMIT 20;
The exact pagination method should match the application’s requirements and database system.
The important point is to retrieve only the data that the application actually needs.
Using OFFSET for Very Large Pagination
LIMIT and OFFSET are easy to understand, but large offsets can become inefficient.
Consider:
SELECT id, name
FROM products
ORDER BY id
LIMIT 20 OFFSET 100000;
The database may have to process or skip a substantial number of rows before returning the requested page.
For large datasets, keyset or cursor-based pagination can be more efficient.
A query can use the last retrieved ID as a starting point:
SELECT id, name
FROM products
WHERE id > 100000
ORDER BY id
LIMIT 20;
The best pagination approach depends on the application’s ordering requirements, but developers should be aware that very large offsets can create performance problems.
Creating Unnecessary JOINs
JOINs are essential for relational databases, but every additional table involved in a query increases its complexity.
A query might join several tables even though the application only needs information from two of them.
Unnecessary joins can increase the amount of work performed by the database and make the query harder for the optimizer to handle efficiently.
Before adding another JOIN, ask whether the related information is genuinely required by the result.
Good database design does not mean using as many joins as possible. It means retrieving the information needed with an appropriate query structure.
Joining Tables Without Proper Conditions
An especially serious mistake is creating an unintended Cartesian product.
For example, joining two tables without the appropriate relationship condition can produce a huge number of combinations.
A query such as:
SELECT *
FROM customers
JOIN orders;
does not clearly specify how the rows should be related.
A proper join condition might be:
SELECT *
FROM customers
JOIN orders
ON customers.id = orders.customer_id;
When the number of rows in both tables is large, an unintended many-to-many combination can create enormous intermediate results and severely affect performance.
Using Correlated Subqueries Excessively
Subqueries can be useful, but certain correlated subqueries may cause repeated work.
A correlated subquery references a value from the outer query, which can lead to the inner operation being evaluated repeatedly.
For some workloads, replacing a correlated subquery with a JOIN, aggregation, or another query structure can produce a more efficient execution plan.
However, there is no universal rule that JOINs are always faster than subqueries. Modern database optimizers can transform queries in different ways.
The right approach is to examine the actual execution plan rather than relying on assumptions.
Sorting Large Result Sets Without Need
ORDER BY can require significant work, especially when a large number of rows need to be sorted.
For example:
SELECT *
FROM transactions
ORDER BY description;
If the application does not actually need sorted output, the ORDER BY is unnecessary.
When sorting is required, an appropriate index may sometimes help, depending on the query and database engine.
Developers should therefore ask whether sorting is necessary and whether the sort column aligns with the access pattern.
Running Repeated Queries Instead of One Efficient Query
Application code sometimes sends many small database queries when one well-designed query could return the required information.
This can create unnecessary network communication and database overhead.
For example, an application may first retrieve a list of customer IDs and then execute another query for each customer. This pattern is often known as an N+1 query problem.
Instead, a JOIN or another set-based query may retrieve the required data in fewer database calls.
Reducing unnecessary round trips can have a significant impact on application performance.
Ignoring the Query Execution Plan
One of the biggest SQL performance mistakes is guessing why a query is slow instead of examining how the database executes it.
Most major relational database systems provide tools that show query execution plans.
An execution plan can reveal whether the database is using an index, scanning a large table, performing an expensive sort, or choosing an unexpected join strategy.
Learning to read execution plans is an important step for developers who work with performance-sensitive applications.
The plan is evidence about what the database is actually doing, which makes it more useful than assumptions based only on the SQL syntax.
Using Poor Data Types
Choosing inappropriate column types can affect storage, comparisons, indexing, and overall database design.
For example, storing numeric values as text can make sorting and calculations more complicated.
Similarly, using unnecessarily large data types for every field can increase storage requirements.
Developers should choose data types based on the values the application actually needs to represent.
Good schema design can improve both correctness and performance.
Fetching Unnecessary Data in Application Code
Database performance is not the only concern. Even when a query executes quickly, returning excessive data can create problems further up the application stack.
Large result sets consume memory, increase network traffic, and may require additional processing by the application.
A good query should consider the full path from database to user interface.
Retrieve the fields and rows required for the current operation rather than treating the database as a source of unlimited data.
Ignoring Database Growth
A query may perform well during development because the database contains only a small number of records.
Production systems can be very different.
As tables grow, full scans, large joins, sorting, and poorly indexed filters can become increasingly expensive.
Developers should think about expected data growth when designing queries and indexes rather than waiting until a performance problem appears.
Testing with realistic dataset sizes can reveal problems much earlier.
Final Thoughts
Slow SQL queries are often caused by small design mistakes rather than one dramatic programming error. Selecting unnecessary columns, forgetting useful indexes, returning too many rows, using inefficient pagination, creating unnecessary joins, repeating database calls, and ignoring execution plans can all contribute to poor performance.
The most important lesson is that SQL performance should be measured rather than guessed.
Developers should understand how their database organizes data, which indexes support common queries, how joins are performed, and what the execution plan reveals about actual behavior.
Writing efficient SQL is not about making every query complicated or trying to optimize every line prematurely. It is about retrieving the right data, using suitable access paths, and designing queries that remain practical as the database grows.
With careful schema design, appropriate indexing, efficient filtering, sensible pagination, and regular performance testing, developers can avoid many common SQL mistakes and build database-driven applications that remain responsive as their workloads increase.
