How to optimize SQL queries in a practical and effective way

When an application starts to run slowly, many people look to the server, the network, or even the user's machine… but in many cases, the real culprit is poorly designed SQL queries . A simple change to a WHERE clause or an index can make the difference between a response time of milliseconds or several seconds.

The tricky part is that pinpointing why a query is slow can take hours or days : reviewing execution plans, testing variations, analyzing blocking issues, statistics, and so on. The good news is that there are very common design patterns that almost always cause performance problems, and recognizing them quickly will save you a huge amount of diagnostic time.

SQL patterns that typically perform very poorly

There are certain SQL code patterns that are real performance bombs and should be closely monitored: complex ORs, wildcard searches in strings, massive writes, queries with too many tables, or abusing hints, among others.

The practical goal is that, when you encounter an application that's running slowly, you can scan the queries and quickly locate those dangerous patterns to focus your testing right where the problem is most likely to be.

You should always validate with real measurements (execution times, logical reads, CPU usage, etc.), but knowing where to start the investigation greatly shortens the process.

Problems with OR in JOIN and WHERE on multiple columns

Logical operators may seem innocent, but using OR on multiple columns or tables can destroy efficiency , especially in systems like SQL Server, although the idea also applies to other engines.

While AND conditions are mutually exclusive and allow for step-by-step reduction of the dataset, OR is inclusive and forces the engine to evaluate each branch separately . In terms of execution plan, this translates into more passes through the tables and, often, an exorbitant number of reads.

The most serious case occurs when the OR operator combines multiple columns or columns from multiple tables . The optimizer is forced to trace each path of the OR operator and then combine the results. In tables with hundreds of thousands of rows, this can lead to millions of logical reads, even if the tables themselves are not that large.

A common tactic for improving these situations is to eliminate the OR clause by rewriting the query into several statements , each with its own SELECT statement, subsequently joined with UNION or UNION ALL. Each SELECT statement can then be optimized individually by the engine.

In practice, this means that simple OR queries can become several slightly longer queries , but in return, the plan is usually more stable, with fewer reads and shorter execution times. The cost is that you're sometimes reading the same tables multiple times; even so, the improvement usually outweighs the cost, especially when the OR clause prevents you from taking advantage of appropriate indexes.

The key is not to rely on OR conditions across different columns or multiple tables . If you're auditing slow queries and see OR clauses scattered throughout the JOIN or WHERE clauses, consider them a prime suspect and test them separately in multiple queries.

Wildcard and full text string searches

Text searches are another classic example of performance problems. Searching for arbitrary substrings within columns of text is inherently expensive : the engine has no way to "jump" to the correct point and ends up scanning row by row and character by character.

For frequently accessed text columns, it's worth considering several basic questions: Are there indexes on these columns? Does the search pattern allow the use of these indexes? Can we use FULLTEXT indexes or an alternative solution like hashes or n-grams?

In database engines like SQL Server or MySQL, placing the wildcard % at the beginning of a pattern breaks the use of conventional B-tree indexes . That is, a WHERE LastName LIKE '%For%' clause forces a full scan, even if you have an index on LastName. The same applies to patterns ending in % but in descending order: the advantage of index order is lost.

In small tables it might not be a problem, but in tables with millions of rows, a sequential scan for each search is a huge bottleneck . That's why the design of these queries needs to be carefully considered.

Before diving into complex optimizations, it's very useful to rethink the functional requirement itself: does the user really need to search anywhere within a string? Sometimes searches by prefix ("For%" instead of "%For%") are sufficient, or you can force the use of other filters (by date, status, category, etc.) that drastically reduce the number of rows that need to be checked.

In addition to adjusting functional design, full-text indexes offer a powerful alternative when text searches are frequent or complex. They allow you to locate words and phrases and perform more advanced linguistic searches using specific operators, and generally work on data structures optimized for text.

However, full-text indexing is an additional feature: it needs to be installed, configured, and maintained . In applications heavily focused on text content, it's often a very worthwhile investment, but it does add complexity and maintenance costs.

For relatively short strings, such as names or codes, n-gram techniques can also be used : each value is broken down into small fragments of fixed length (e.g., 3 characters) which are stored in a separate table, along with a reference to the original row.

In this way, instead of scanning a huge NVARCHAR table , an exact search is performed on the n-gram table, which can be well indexed. The original rows are then retrieved using the identifier. The cost of this technique is twofold: firstly, the n-gram table can grow very quickly , and secondly, it must be maintained with every insertion, update, or deletion, making it reasonable only for short texts.

In summary for this block: searches with internal wildcards are expensive by design , and the best thing you can do is adapt the application design (remove initial wildcards, add filters) or use specialized structures like FULLTEXT or n-grams when there really is no alternative.

Indexes: types, design and maintenance

If there's one performance lever that keeps coming up, it's indexing. The most direct way to speed up a query is to create appropriate indexes on the columns that appear in the WHERE clause and the JOIN conditions . But, like everything in databases , there's a catch: too many indexes are also a problem.

An index is basically a structure (usually a B-tree) that allows you to quickly locate rows that meet a condition without having to read the entire table. MySQL, for example, stores most of its indexes in B-trees: PRIMARY KEY, UNIQUE, INDEX, and also FULLTEXT (although they have their own internal characteristics).

In addition to B-tree-based indexes, MySQL uses R-trees for spatial data and hash indexes for in-memory tables . Each structure has its advantages depending on the data type and access pattern: B-trees are ideal for ranges and sorting; R-trees for spatial queries; and hash indexes for very fast in-memory equality lookups.

Regarding types of logical indexes, the most common are: primary keys, foreign keys, unique indexes, normal indexes, multi-column indexes, full-text indexes, and functional indexes . Each one serves a different need, and it's important to understand their implications for both reading and writing.

For example, a composite index on (contact_last_name, contact_first_name) will be useful when searching by last name only or by both last name and first name , but it won't help if you filter only by first name. These kinds of details make a big difference when designing table indexes.

There is also the option to index only a prefix from a text column to reduce the index size. If `nombre_cliente` is VARCHAR(50), it might be sufficient to index the first 20 or 25 characters, provided that most values ​​are reasonably distinguishable within those characters. The goal here is to find a balance between selectivity and index size.

In MySQL, starting with version 8.0.13, you can create functional indexes on the result of an expression . This is especially useful for queries that use functions like YEAR(payment_date) in the WHERE clause: instead of breaking the index on the payment_date column, you create a direct index on YEAR(payment_date), and the optimizer can take advantage of it.

Indexes can be created with CREATE INDEX, ALTER TABLE, or directly in the table definition with CREATE TABLE . Each option is useful at different times: initial schema creation, subsequent refactoring, or specific performance adjustments.

It's also crucial to be able to inspect which indexes exist and how they're being used . In MySQL, you can use SHOW INDEX or DESCRIBE to see indexes and key types, and commands like EXPLAIN to check if a query is actually using those indexes or is still performing a full scan (type = ALL, very high row count, etc.).

On the maintenance side, you have tools like OPTIMIZE TABLE and ANALYZE TABLE . OPTIMIZE helps defragment the table and reorganize indexes, while ANALYZE recalculates the key distribution, which is the basis for many of the optimizer's decisions (join order, index selection, etc.). Running them after massive loads or large changes helps keep execution plans reasonable.

However, there are common mistakes to avoid: over-indexing a table, leaving it with almost no indexes, or lacking a clustered index/primary key . Too many indexes penalize writes (each INSERT, UPDATE, or DELETE operation must update all of them) and take up a lot of disk space and backup resources. Too few indexes, on the other hand, force continuous full table reads.

In SQL Server, the engine itself also suggests missing indexes based on execution plans , either through Management Studio, the plan's XML file, or dynamic views. These recommendations are useful as a starting point, but they should be reviewed critically: they often propose overly large indexes with many INCLUDE columns, or duplicate similar indexes.

Before accepting a missing index suggestion, it's worth asking yourself: Does a similar index already exist that could be extended? Do I need all INCLUDE columns? What is the estimated improvement impact? Is this query run often enough to justify it?

Finally, if your application has tables without a clustered index or primary key, that should raise a red flag . Pure heaps typically perform worse for many workloads and make it difficult to create efficient non-clustered indexes. Defining a good primary key and a clustered index is usually a high priority before getting into finer tuning.

Massive writing, blocks, and log growth

It's not all about SELECT statements. Large-scale write operations can also cause serious performance and contention issues . Large updates, inserts, or deletes can lock entire tables for extended periods, drastically increase the size of the transaction log, and leave other users waiting.

Every time you modify data, the engine places locks to ensure consistency and prevent conflicts . This is good for integrity, but when an operation takes too long, it becomes a bottleneck: other queries are blocked, timeouts occur, and complaints about "the database is terrible" soon follow.

What constitutes a "large operation"? It depends heavily on the schema: number of indexes, triggers, foreign keys, etc. In a simple table, 100.000 rows might be manageable in a single transaction; in a table with many constraints, 2.000 rows could already be a problem. The only reliable way to know is to test it under real-world or very similar conditions.

In addition to locks, massive writes cause the transaction log to grow rapidly . If you don't monitor its size, you could end up with a full log or even the disk itself. This is especially critical during maintenance tasks, ETL loads, or migrations, where many writes occur in a short period.

A sensible approach is to break large operations into smaller batches . Instead of updating a million rows at once, you do it in batches (for example, 10.000) with intermediate commits, thus reducing lock durations and the size of each transaction in the log. For off-peak processes (maintenance windows), you can afford larger batches; in production, you may need to be quite conservative.

It's also worth reviewing which operations generate massive writes: adding and populating new columns, changing data types, imports, historical files, and periodic cleanups . Understanding their impact helps you plan for different timeframes, adjust batch sizes, and avoid unpleasant surprises during a deployment or critical maintenance.

Queries with many tables and plan explosion

Another pattern that causes quite a few headaches is the use of massive queries that join a huge number of tables . SQL optimizers (SQL Server, Oracle, DB2, MySQL, etc.) are designed to find a "good" plan quickly, but the search space grows exponentially with each additional table.

In a query with many tables, the optimizer has to decide the order in which to join them, what type of join to use in each case, when to apply filters and aggregations , and so on. The number of possible plans grows factorially, or even worse, depending on the shape of the join tree (more linear or more branched).

For example, with about 12 tables you can already eliminate tens of billions of possible theoretical plans if the query is very dense. Obviously, the optimizer doesn't explore them all, but it has to quickly narrow down many options and sometimes settles on a candidate that isn't the best, simply because it can't dedicate more time to searching.

This doesn't mean all complex queries are bad, but each additional table increases the risk that your chosen plan won't be optimal . Furthermore, maintaining and debugging SQLs with 20, 30, or 40 tables is a nightmare for any team.

Strategies to improve this scenario include: moving metadata or search tables to separate queries that dump their results into temporary tables, eliminating unnecessary joins, splitting a query into several smaller ones, and, in very common use cases, creating indexed views that pre-calculate some of the work.

When you split a large query into several smaller ones, you must ensure that there are no significant data changes between them that would invalidate the result . This may require the use of transactions, appropriate isolation levels, or explicit locks, depending on the engine and the criticality of the data.

In many cases, however, it's possible to reorganize data retrieval into smaller, more understandable logical units : first, you retrieve a key subset (for example, the IDs that meet certain conditions), and then, in a second query, you retrieve the details. This also allows you to remove unnecessary columns and simplify the logic.

General best practices for writing queries

Beyond very specific patterns, there are a number of general recommendations that tend to improve performance quite consistently. For example, avoid `SELECT *` and select only the columns you actually need . Each additional column means more data moving across the network, more memory, more I/O load, and sometimes prevents you from using certain indexes optimally.

Another important point is to avoid overusing DISTINCT and UNION when they are not needed . Both operators involve sorting or deduplication operations, which are among the most expensive parts of a query. In many situations, UNION ALL (which does not deduplicate) is sufficient and much faster.

Regarding JOINs, it's preferable to use INNER JOINs when you don't actually need "orphan" rows from one of the tables . Outer joins (LEFT/RIGHT OUTER JOINs) restrict the optimizer's flexibility and often lead to less efficient plans. Furthermore, predicates from the outer table should be placed in the ON clause, not the WHERE clause, so the optimizer can apply them correctly.

It is also advisable to duplicate constant conditions on the joined columns of both tables when possible (for example, A.id = B.id and A.id IN (10,12) and B.id IN (10,12)). This gives the optimizer additional clues about the range of relevant values ​​in each table and can improve index selection and execution order.

The ORDER BY clause should only be used when you truly need to order the results. Without ORDER BY, the order of the returned set is not guaranteed , even if it sometimes appears to be by chance. Every ORDER BY clause implies an ordering, and with large result sets, this can become one of the most expensive steps in the query.

In Oracle, you can also use common table expressions (CTEs) and specific regular expression syntax to help the optimizer create more efficient intermediate temporary tables. Rewriting certain queries using well-designed CTEs can allow the optimizer to "push" predicates down into the views, filtering data earlier and reducing the size of subsequent joins.

EXPLAIN, statistics and suggestions for consultation (hints)

One tool that should be in every toolbox is EXPLAIN (and its variants like EXPLAIN ANALYZE). EXPLAIN shows you how the engine plans to execute a query : which indexes it uses, the access type (ALL, index, ref, range…), how many rows it estimates it will read, the join order, and so on.

With that information you can see, for example, whether your brand new country index is being used or not , whether the query is still doing a full scan of the table (type = ALL), whether rows is a ridiculously high number, or whether a FULLTEXT is coming into play instead of a LIKE search.

In MySQL, after creating an index and running EXPLAIN again, you should see a change in the `type` column towards more selective values ​​(ref, range, etc.) and a noticeable reduction in the row estimate . These before-and-after comparisons are invaluable for verifying whether your optimization is working.

Another key element is the distribution of values ​​statistics (ANALYZE TABLE, automatic statistics updates, etc.). The optimizer largely decides which plan to choose based on these statistics; if they are outdated, you can end up with very poor plans. After large loads or massive changes, it's advisable to explicitly update the statistics.

Regarding query suggestions, or hints, the prudent approach is to use them as a last resort and with great restraint . A hint is an explicit instruction to the optimizer: it forces a type of join (MERGE, HASH, LOOP), a parameter value to optimize (OPTIMIZE FOR), an isolation level (NOLOCK), prevents plan reuse (RECOMPILE), etc.

The problem is that a hint that fixes a borderline case today can become an obstacle tomorrow when the data, schema, or usage patterns change. Furthermore, they can mask deeper problems: missing indexes, unnecessary data volume, poorly designed business logic, and so on.

Some typical warnings: NOLOCK can return inconsistent data (dirty reads), so it shouldn't be used where data quality matters; RECOMPILE on a very frequent query can generate a brutal overhead; forcing HASH/MERGE/LOOP limits the optimizer's options and can lead to horrendous plans in the medium term; and OPTIMIZE FOR can become obsolete as soon as the application's usage patterns change.

The sensible way to work is to first exhaust the "clean" alternatives (appropriate indexes, SQL rewriting, parameter adjustments, updating statistics) and only if there is no other way out, apply a very specific hint, well documented and reviewed periodically.

Overall, optimizing SQL queries is a mix of thoroughly understanding how the engine works, recognizing risky patterns (complicated OR statements, misplaced wildcards, excessive tables, poorly designed indexes), and leveraging tools like EXPLAIN and system views to make informed decisions. By combining sound index design, clean queries, and a degree of judgment to avoid typical anti-patterns, your databases will respond much more quickly without the need for monstrous hardware or miracle fixes.