Quick and Practical Guide – How to write the perfect and optimized query?

If you’re a DBA, DevOps, Tech Lead, or Infrastructure Manager, you know that database health is the beating heart of any application. But why do systems that seem robust suddenly become slow, inefficient, or even fail? The answer, more often than not, isn’t in the infrastructure, but in something much more subtle and dangerous: the queries that run in production.

A poorly written query is like a slow leak in a pipe. At first, you don’t even notice. Over time, the problem worsens, leading to slowdowns, excessive resource consumption, and, in a peak scenario, the dreaded downtime. If you’ve spent hours trying to understand why a simple report takes minutes to load, this article is for you.

Here, we’ll dive into the most common mistakes professionals make when writing queries, and more importantly, we’ll show how HTI Tecnologia has helped medium and large companies master the art of query optimization, ensuring performance, availability, and security in their data environments. This guide is your arsenal against malicious queries.

1. The Discomfort of De-optimization: Why Your Queries Are Your System’s Achilles’ Heel

The cost of a slow query goes far beyond waiting time. It impacts the user experience, generates unnecessary infrastructure costs (yes, you pay more for your cloud because of inefficient queries), and consumes valuable time from your IT team. Additionally, a lack of optimization can lead to serious security issues and even financial losses. A single query can be the difference between a successful transaction and an abandoned shopping cart.

2. Critical Mistake 1: Ignoring the Power of Indexes (And why your search query is slow)

The search query is the most common type, and the lack of proper indexes is the biggest enemy of its speed. Trying to find information in a gigantic table without indexes is like looking for a needle in a haystack. The query performs a full table scan, meaning it reads every row of the table to find what you need. In tables with millions of records, this query becomes an unbearable bottleneck.

How HTI Tecnologia Optimizes the Use of Indexes?

Our specialized DBAs perform a complete diagnosis of your database. We analyze the access patterns of each query and strategically suggest the creation, alteration, or removal of indexes. Through database and performance tuning consultancy, we ensure that every query uses the shortest and most efficient path.

• Identification of problematic queries: We use monitoring tools to identify the queries that most impact your system’s performance.
• Analysis of execution plans: We examine how the database is processing your queries to identify bottlenecks.
• Creation of ideal indexes: We suggest composite, partial, and other types of indexes that best suit your query.

SELECT * FROM produtos WHERE nome_produto = 'Smartphone X';


CREATE INDEX idx_produtos_nome ON produtos (nome_produto);


SELECT * FROM produtos WHERE nome_produto = 'Smartphone X';

3. Critical Mistake 2: Using SELECT * Indiscriminately (The excess that weighs down your query)

This is a classic. The SELECT * query is practical during the development phase but a nightmare in production. By selecting all columns from a table, even if your query only needs two, you force the database to read unnecessary data, consuming bandwidth and server memory. Every extra byte counts, and in queries that run thousands of times, this adds up.

HTI’s Strategy to Optimize SQL Code

The solution is simple: select only the columns your query actually needs. HTI Tecnologia, in its consultancy projects, works to educate and implement this practice.

-- Incorrect: Query that overloads the system
SELECT * FROM pedidos WHERE status = 'pendente';

-- Correct: Query optimized for performance
SELECT id, nome_cliente, data_pedido FROM pedidos WHERE status = 'pendente';

4. Critical Mistake 3: Handling JOINS Inefficiently (And the impact on your complex query)

Queries that use multiple tables are common, but the improper use of JOIN can be the source of major problems. Optimizing JOINs is essential to avoid the famous Cartesian product and ensure that the joins occur efficiently. A poorly executed JOIN can turn a simple query into a monstrous operation.

Strategies to Optimize JOINS and your query

• Use INNER JOIN whenever possible: It is more efficient than OUTER JOIN, and your query will run faster if you only need records with a match.
• Optimize the joining tables: Make sure the columns used for the join have indexes. A query that JOINs without indexes is a slow query.
• Avoid unnecessary subqueries: Often, a subquery can be replaced by a more performant JOIN, resulting in a more elegant and faster query.

SELECT c.nome, p.data_pedido, p.valor
FROM clientes c
JOIN pedidos p ON c.id_cliente = p.id_cliente;


CREATE INDEX idx_clientes_id ON clientes (id_cliente);
CREATE INDEX idx_pedidos_id_cliente ON pedidos (id_cliente);


SELECT c.nome, p.data_pedido, p.valor
FROM clientes c
JOIN pedidos p ON c.id_cliente = p.id_cliente;

5. Critical Mistake 4: Not Having a 24/7 Support Routine (The lack of care that makes every query obsolete)

Query optimization is not a one-time event. It is a continuous process that requires constant monitoring and adjustments. What works today may not work tomorrow as the data volume and usage patterns change. The lack of a database support routine can lead to unexpected and costly problems. A query that was once fast can become slow overnight.

Why Outsource Database Support?

Outsourcing DBA services to a company like HTI Tecnologia is a strategic decision. Instead of your IT team being overloaded with maintenance routines, you have a team of specialists, 24 hours a day, 7 days a week, dedicated to ensuring the availability, performance, and security of your data environment. This frees up your internal team to focus on the company’s core business and innovation projects.

HTI offers proactive and predictive support, anticipating failures and resolving incidents before they cause a negative impact. Read more about how 24/7 support can transform your IT operation.

6. Critical Mistake 5: Not Using the WHERE Clause Correctly

The WHERE clause is the soul of your search query, but many professionals make mistakes that prevent the database from using indexes. Operations like LIKE '%value' (with the wildcard at the beginning), the use of functions on indexed columns (WHERE YEAR(data) = 2023), or incorrect type comparisons make your query inefficient, forcing a full table scan. An optimized query uses the WHERE clause in a way that allows the database to use indexes.

Optimizing the WHERE clause

• Avoid wildcards at the beginning of LIKE: If possible, use LIKE 'value%' so that your query can use the index.
• Do not apply functions to columns: Instead of WHERE YEAR(data) = 2023, use WHERE data BETWEEN '2023-01-01' AND '2023-12-31'. This allows your query to use the index.

-- Incorrect: Using a function on an indexed column (prevents index use)
SELECT * FROM vendas WHERE YEAR(data_venda) = 2023;

-- Correct: Using a date range (allows index use on the data_venda column)
SELECT * FROM vendas WHERE data_venda >= '2023-01-01' AND data_venda < '2024-01-01';

-- Incorrect: Wildcard at the beginning of LIKE (prevents index use)
SELECT * FROM clientes WHERE nome_cliente LIKE '%Silva%';

-- Correct: Wildcard at the end of LIKE (allows index use if the column is indexed)
SELECT * FROM clientes WHERE nome_cliente LIKE 'Silva%';

7. Critical Mistake 6: Using Inadequate Data Types

The choice of a column’s data type can have a significant impact on your query’s performance. Using a VARCHAR(255) when a VARCHAR(50) would suffice, or an INT when a TINYINT works, wastes disk space and memory. The database needs to process more data, making each query slower. HTI, in its database consultancy, analyzes data modeling to ensure that data types are the most efficient for each query and for the system as a whole.

-- Original table with an inadequate data type for 'age'
CREATE TABLE usuarios_errado (
    id INT PRIMARY KEY,
    nome VARCHAR(255),
    idade INT -- INT takes up more space than necessary for ages
);


-- Optimized table with an adequate data type for 'age'
CREATE TABLE usuarios_correto (
    id INT PRIMARY KEY,
    nome VARCHAR(255),
    idade TINYINT -- TINYINT is sufficient for ages (0-255)
);


-- For a column that stores ZIP codes, a VARCHAR(8) may be more suitable than VARCHAR(255)
ALTER TABLE enderecos ALTER COLUMN cep TYPE VARCHAR(8);

8. Critical Mistake 7: Not Limiting Results with TOP or LIMIT

A query that returns thousands of records when the user only needs the first 10 is a colossal waste of resources. Always use TOP (SQL Server) or LIMIT (MySQL, PostgreSQL) to restrict the number of records returned. This is fundamental for the performance of queries in dashboards and reports. A query with LIMIT is almost always faster than a query without it.

-- Query that returns all products (potentially many)
SELECT id, nome_produto, data_cadastro FROM produtos ORDER BY data_cadastro DESC;

-- Query optimized to return only the 5 most recent products (with LIMIT/TOP)
-- For MySQL/PostgreSQL:
SELECT id, nome_produto, data_cadastro FROM produtos ORDER BY data_cadastro DESC LIMIT 5;

-- For SQL Server:
SELECT TOP 5 id, nome_produto, data_cadastro FROM produtos ORDER BY data_cadastro DESC;

9. Critical Mistake 8: Lack of Execution Plan Analysis

You may have written the most elegant query in the world, but if you don’t understand how the database executes it, it can be a disaster. The EXPLAIN command (or EXPLAIN ANALYZE, depending on the DBMS) is the most powerful tool for analyzing a query’s performance. It shows the execution plan, detailing how the database accesses data, uses indexes, and performs joins. HTI uses this analysis as the basis for all query optimizations.

• Identify full table scans: Look for operations that scan the entire table, which indicate a lack of indexes.
• Analyze join types: Understand if the database is using an efficient join algorithm (nested loop, hash join, merge join).
• Verify index usage: The execution plan should show that indexes are being used for search operations, validating your query.

-- Example of query for analysis
SELECT * FROM produtos WHERE nome_produto LIKE 'Notebook%';


-- Using EXPLAIN to see the execution plan (for MySQL/PostgreSQL)
EXPLAIN SELECT * FROM produtos WHERE nome_produto LIKE 'Notebook%';

-- Simplified output example (variable among DBMS, but illustrative)
-- id | select_type | table    | partitions | type | possible_keys        | key             | key_len | ref  | rows | filtered | Extra
-- 1  | SIMPLE      | produtos | NULL       | ref  | idx_produtos_nome    | idx_produtos_nome | 258     | NULL | 10   | 100.00   | Using index condition

-- If the 'type' is 'ALL' and 'key' is NULL, it means a full table scan and the index is not being used.
-- If the 'type' is 'ref' or 'range' and 'key' shows the correct index, it indicates good optimization.

-- For SQL Server, the command is slightly different, usually using Management Studio or Azure Data Studio for "Display Estimated Execution Plan" or "Display Actual Execution Plan".
-- Or via code:
SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM produtos WHERE nome_produto LIKE 'Notebook%';
GO
SET SHOWPLAN_TEXT OFF;
GO

The Definitive Solution: HTI Tecnologia’s Expertise

Writing the perfect query is a continuous challenge. It requires in-depth knowledge of the DBMS, business rules, and optimization best practices. Having an experienced and qualified DBA is a competitive advantage.

HTI Tecnologia is the ideal partner for companies seeking excellence in their data environments. With our team of specialists, we guarantee not only query optimization but the complete management of your data environment, with a focus on:

• Proactive monitoring: Detection and correction of problems in each query before they become critical.
• Performance tuning: Constant optimization to ensure the best performance for each query.
• Operational continuity: Reduced risk of downtime and unavailability.
• Security: Protection of your data against threats.

We are a benchmark in SQL and NoSQL database consultancy (MySQL, MariaDB, PostgreSQL, Oracle, SQL Server, MongoDB, Redis, Neo4J) and have a proven track record of success, with over 35 years of experience in the market. Check out our testimonials page and see how we’ve transformed our clients’ operations.

If you are tired of slow queries, unstable systems, and wasting resources, it’s time to act.

The time has come to transform your data infrastructure!

Don’t let your database performance compromise your business’s success.

Talk to one of our database specialists and discover how HTI Tecnologia can help your company achieve excellence in performance, availability, and security.

chedule a meeting here

Visit our Blog

Learn more about databases

Learn about monitoring with advanced tools

Have questions about our services? Visit our FAQ

Want to see how we’ve helped other companies? Check out what our clients say in these testimonials!

Discover the History of HTI Tecnologia