poorly worded SQL queries These are one of the most frequent reasons why an application runs sluggishly when working with large relational databases like MySQL, PostgreSQL, SQL Server, Oracle, or DB2. Although we now have powerful servers and elastic clouds, inefficient queries will ultimately cost you. higher infrastructure costs, higher latency, and a worse user experience.
Optimizing SQL queries in large databases goes far beyond simply "adding an index and that's it." It involves Understanding how the query optimizer thinksHow data is stored, what access patterns your application uses, and what combined techniques allow you to reduce I/O, CPU, and memory usage. In the following sections, we'll review, in considerable detail and with examples, The most effective strategies to get the most out of your relational databases.
What is SQL query optimization, really, and why does it matter?
Optimize an SQL query This means rewriting it (and adjusting its context: indexes, statistics, design) so that the engine returns the same result while consuming fewer resources and in less time. SQL syntax allows many ways to express the same thing, but not all of them execute equally fast, especially when there are millions of rows or complex joins.
When a developer understands how it works query planner With your engine (PostgreSQL, MySQL, SQL Server, Oracle, DB2, etc.), you can write queries that make better use of indexes, reduce unnecessary reads, and minimize costly operations such as sorting, sequential scans, or repetitive correlated subqueries.
However, it's important to be clear that the Query optimization is not the only performance factorThe schema design (normalization, primary and foreign keys, data types), the architecture (replicas, partitions, caches), and the infrastructure itself have a significant impact. But even with a decent architecture, a single poorly optimized query can be a major problem. brutal bottleneck.
Among the benefits of working in consultations, the following stand out: overall performance improvement (more requests handled in less time), the cloud cost reduction (less CPU and disk, smaller instance sizes) and a smoother user experience by reducing wait times in listings, searches, and reports. Furthermore, clear and well-structured queries are easier to maintain and debug, something that is greatly appreciated when the project grows.
In applications that truly aim to scale, continuous query optimization becomes a recurring task: monitor, detect, measure, adjust and remeasureIt is not a one-off action, but a process.
Practical example: same query, very different performance
To bring your ideas down to earth, imagine a table orders with more than 20 million records In an e-commerce site, we want to retrieve a customer's completed orders from the last 30 days, and without much thought, we could write something like this:
SELECT * FROM pedidos
WHERE cliente_id = 456
AND LOWER(estado) = 'completado'
AND fecha_creacion BETWEEN NOW() - INTERVAL '30 days' AND NOW();
This query returns what we want, but from a performance standpoint it's a bit of a mess: it's using SELECT *, applies a function (LOWER) on a filter column and combines dates with expressions that can interfere with the use of indexes. If, in addition, suitable indexes do not exist on client_id, status or creation_date, the engine will be forced to scan a large part of the table.
The practical consequences are clear: More data transferred than necessaryMore work for the backend mapping unused columns, a lot of disk reading, and execution time that in very large tables can skyrocket to several seconds, affecting the entire system when launched many times.
The same question, phrased more intelligently, could look like this:
SELECT id, fecha_creacion, total
FROM pedidos
WHERE cliente_id = 456
AND estado = 'Completado'
AND fecha_creacion >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY fecha_creacion DESC
LIMIT 100;
Here we are selecting only the necessary columnsavoiding functions on the status column, simplifying the date condition, and limiting the number of rows. With well-designed indexes (for example, INDEX(cliente_id, fecha_creacion) and one about estado (if it has a high cardinality), the engine can use index scans and resolve the query in milliseconds instead of seconds.
This contrast illustrates a key idea: It is not enough for the query to "work"You have to worry about how it runs when the table no longer has hundreds of rows, but millions.
Indexes: the main lever for accelerating searches
The Indexes are the most powerful tool for speeding up queries in large databases. Instead of traversing the entire table row by row (sequential scanning or Seq Scan), the engine uses auxiliary structures (usually B-trees, R-trees or hashes, depending on the data type and engine) that allow jumping directly to candidate rows.
In MySQL, for example, the most common structures are trees B for type indices PRIMARY KEY, UNIQUE, INDEX y FULLTEXT, while spatial indices use R trees and in-memory tables can pull from indexes based on hashEach one is optimized for a specific access pattern.
However, it's not about putting an index on everything. Each additional index It takes up disk space and slows down insertions, updates, and deletions.because the engine must keep the structure synchronized. The trick is finding the balance between number of indices and response time, focusing on critical reading queries.
Among the most common types of indexes in relational engines we find those of primary key (uniquely identify each row and do not allow null values), those of foreign key (reference the PK of another table), the unique indexes (guarantee uniqueness, but do allow nulls) and the composite indices on several columns, very useful when filtering or sorting by more than one field at a time.
There are also scenarios where it is useful to use indices with repeated values (to speed up searches in non-unique columns) or full-text indexes (FULLTEXT in MySQL, for example) to improve searches in long text fields. Since MySQL 8.0.13, these can be created functional indicesThat is, on the result of an expression or function (for example, YEAR(fecha_pago)), which opens the door to advanced optimizations.
We can create indexes in MySQL with different statements: CREATE INDEX, adding them later; ALTER TABLEto modify an existing table; or directly in the definition with CREATE TABLEIn all three cases, simple, composite, unique, and prefix indices are allowed (only the first N characters of a VARCHAR) or FULLTEXT, depending on the design we need.
The use of prefix indices This is useful when we have long strings, but a relatively small number of characters is enough to distinguish virtually all the values. This way, we reduce the size of the indexes without losing too much selectivity, which is very useful in columns like customer names where we can index, for example, the first 25 characters instead of the entire field.
Select only the columns you need
Abuse SELECT * It's one of the most common bad habits in SQL. It's convenient during development, but in production it becomes a burden: Each extra column implies more bytes traveling from the database up to your application, more memory on the client and more deserialization work.
When a table contains large columns (BLOBs, large JSON files, huge text files, binary avatars, etc.), including them unnecessarily increases I/O and RAM usage. Furthermore, in engines like PostgreSQL, limiting the number of columns allows for better performance. Index Only Scan, where the database responds from the index without going to the heap, but this only works if all the columns you are requesting are in the index.
A classic example: a table users with columns like id, email, password_hash, avatar, created_at, last_loginIf you throw SELECT * FROM users WHERE email = '[email protected]';You'll be getting the password hash and binary avatar even if you only want to show the email and last login date. It's much better to just ask for that. id, email, last_login.
Always work with explicit column lists It makes your queries clearer, protects you against schema changes (adding a column doesn't break anything), and dramatically reduces resource consumption in large tables or paginated lists, helping to manage large amounts of data.
JOINs, subqueries and CTEs: how to properly structure complex queries
correlated subqueries (Those that are executed once for each row of the outer query) may seem elegant on paper, but in practice they become a performance bottleneck as tables grow. Each row in the main table triggers an additional execution of the subquery, resulting in astronomical numbers of operations.
Whenever possible, it is preferable to transform these subqueries into well-indexed JOINs or in CTEs (Common Table Expressions) that break down the logic into clear steps. The optimizer usually handles a combination of tables much better than a nest of complex subqueries.
For example, to retrieve products along with their category name, instead of performing a subquery in the SELECT It is more efficient to use a JOIN against the categories table. If the join columns are indexed (for example, productos.categoria_id y categorias.id), the engine can solve the join with very low cost even on large tables.
CTEs (WITH ... AS (...)These are especially useful in reporting queries, complex aggregations, and step-by-step logic. While they don't always improve performance on their own, they do help the planner and, above all, improve readability, facilitating further optimizations such as adding specific indexes or materializing intermediate results.
Pagination and LIMIT to tame large volumes
In real-world applications, returning thousands of rows at once almost never makes sense from a user experience perspective. A product listing, order history, or event log is typically consumed page by page, so limit the number of rows returned It is a basic requirement for climbing.
The classic approach uses LIMIT y OFFSET (for example, LIMIT 10 OFFSET 20 to go to the “third” page). It is easy to implement and understand, but it has a serious problem: the engine has to traverse all rows prior to the OFFSET in the same way.even though it only returns the final 10. In very large tables, high OFFSET values result in increasingly worse response times.
When working with hundreds of thousands or millions of rows, it's usually better to Keyset Pagination or seek-based paginationIn this approach, instead of telling the database "skip 1000 rows", you tell it "return the next N records starting from this sorted key value", using conditions of the type WHERE fecha_creacion < <última_fecha_vista> with a ORDER BY consistent.
This technique allows the engine to take advantage of a direct index on the sorted column (for example, fecha_creacion o id), avoiding the cost of traversing intermediate pages. Furthermore, it makes pagination stable against insertions or deletions between pages, something that OFFSET does not guarantee.
In return, keyset pagination has the disadvantage that It is not trivial to jump to page 37 Without additional information, since it works forward from a logical cursor (the last ID or date retrieved). That's why many systems combine both approaches depending on functional needs.
Avoid functions in filtered columns and make good use of the WHERE clause
A very common source of performance loss is applying functions on columns that participate in filtersExpressions like LOWER(nombre), DATE(fecha) o CAST(campo AS ...) within the clause WHERE They usually prevent the optimizer from using the index of that column.
Instead, it's better normalize the data when inserting or updating (for example, saving emails in lowercase, statuses with a homogeneous encoding) and transform the input values to match that format, instead of applying the function to the column in each comparison.
It's also worth paying attention to the clause itself. WHERE to make it as selective as possible. Although the order of the conditions doesn't always have a direct impact (the optimizer usually reorders them), it does help to have well-indexed predicates and simple comparisons instead of expensive patterns like LIKE '%texto'which normally force a full scan.
When you need to remove duplicates, consider whether a DISTINCT or if the query could be redesigned with JOINs more precise or uniqueness constraints in the model. Both DISTINCT , the UNION usually involve sorting or grouping operationswhich are among the most expensive in the implementation plan.
Maintaining indexes and statistics to help the optimizer
Modern database engines rely on internal statistics To estimate how many rows meet each condition, which indexes are most appropriate, and in what order to join tables. If these statistics are outdated, the scheduler can make very poor decisions and generate inefficient execution plans.
That's why it's important to periodically run commands like ANALYZE (or their specific variants in each engine) for Refresh statistics after massive loadsmigrations or large volumes of INSERT, UPDATE y DELETEIn PostgreSQL, for example, autovacuum is usually handled automatically, but after a large import it can be useful to run a ANALYZE manual.
In MySQL we have statements like ANALYZE TABLE, which analyzes and stores the key distribution to help the optimizer decide the order and use of indexes in the JOINsAdditionally, OPTIMIZE TABLE allow defragment tables, reorder and update indexes, something recommended in tables that have undergone many changes.
To check if the engine is using the indices as expected, there's nothing like pulling from EXPLAIN o EXPLAIN ANALYZEThese tools show us the estimated plan (and in some engines, also the actual plan with times and rows read) and indicate if a sequential scan is being performed (ALL in MySQL, for example) or if a Index Scanhow many rows are expected and how many are actually played.
Learning to read these plans is perhaps one of the most valuable skills for anyone who wants to optimize databases: It allows you to detect bottlenecks, useless indexes, poorly selective filters, and poorly ordered joins. long before the problem reaches production.
Full-text indexes, regular expressions, and special scenarios
when you work with large text fields (descriptions, rich HTML content, comments, etc.), searches with LIKE '%palabra%' These quickly become impractical for large tables. For these cases, engines like MySQL offer indexes of type FULLTEXT and operators such as MATCH() AGAINST()which allow for much more efficient and relevant searches.
With FULLTEXT You can choose between different modes: natural language, Boolean (with operators) +, -, *(quotation marks for exact phrases, etc.) or query expansion to expand related results. This allows you to build quite powerful internal search engines without having to leave the database.
There are more advanced scenarios where the text includes, for example, embedded HTML tags. In that case, it may be necessary to combine an index. FULLTEXT with functions like REGEXP_REPLACE to clean up labels when comparing exact phrases. A typical strategy is filter first using the full-text index and then apply the regular expression in a second condition to narrow down the result to the exact amount without scanning the entire table.
Other engines, such as Oracle, allow the use of regular table expressions These features help the optimizer insert predicates within views and reduce the intermediate data volume as quickly as possible. This approach is very useful when working with many nested views or complex definitions in collaborative work environments.
Additional best practices: parameters, materialized views, and query splitting
Beyond indices and implementation plans, there are a number of good cross-cutting practices which contribute to both performance and safety. One of the most important is use parameterized queries Instead of concatenating strings to build dynamic SQL, this reduces the risk of SQL injection and allows the database to reuse execution plans for queries with the same structure.
In systems with very heavy and repetitive queries (dashboards, executive reports, aggregate calculations), the materialized views They are a great ally. Unlike a normal view, they physically store the query result, becoming a kind of pre-calculated table that can be indexed and queried very quickly.
PostgreSQL, Oracle, and SQL Server (with their indexed views) natively support materialized views, with various refresh options (manual, scheduled, and even automatic in some cases). In MySQL, since there is no direct support, this behavior is usually emulated with tables and processes that periodically regenerate the data, often through triggers or scheduled tasks.
When a query joins too many tables or relies on a complex mosaic of views, another valid strategy is divide the query into several stepsThis translates to running an initial query to obtain a smaller set (e.g., the relevant IDs) and then running additional queries to complete the information. This approach should be used judiciously, as it can increase the number of database accesses, but in some cases, it drastically reduces the complexity of the plan and the size of the intermediate sets.
Throughout this process, monitoring tools such as pg_stat_statements, PgHero, PMM, Query Store, New Relic or Datadog They can help you quickly identify which queries are slower or run more frequently, so you can prioritize optimization efforts where it really matters.
Optimize SQL queries with the help of AI
In recent years there have appeared tools based on artificial intelligence that analyze your queries and the database schema to propose improvements: index suggestions, query rewrites, changes in table structure, etc. Names like EverSQL, DBScoop, PGAnalyzer or Redshift Advisor have become popular in professional environments.
These solutions can review large volumes of query logs, cross-reference them with statistics, execution plans, and performance metrics, and from there detect inefficient patterns or bottlenecks that would escape us at first glance. They also help to assess the hypothetical impact of creating or eliminating certain indices.
However, it is important to understand them as a support, not as a substitute It depends on your SQL knowledge and understanding of your application. You might receive an index suggestion that, in theory, speeds up a specific query but significantly worsens writes to a critical module. Without business context, the tool doesn't know what matters most.
The ideal combination is a team that masters optimization principles (plans, indexes, normalization, access patterns) and uses AI to accelerate analysis and validate hypothesesnot to make blind decisions.
When you internalize this whole set of techniques—careful index design, minimal column selection, intelligent use of JOINs and CTEs, efficient pagination, regular maintenance of statistics, exploitation of materialized views, and even support from AI tools— Large databases are no longer an uncontrollable monster and they become a predictable and scalable component of your architecture, capable of growing with your business without ruining the user experience or the infrastructure budget.
