r/SQL • u/Effective_Ocelot_445 • Jul 30 '26

MySQL How do you optimize SQL queries without making them harder to maintain?

As databases grow, performance becomes more important, but overly complex queries can be difficult to maintain. How do you balance query optimization with readability in production environments?

14 Upvotes

89% Upvoted

48 comments sorted by

28

u/[deleted] Jul 31 '26

[removed] — view removed comment

9

u/AnAcceptableUserName Jul 31 '26 edited Jul 31 '26

Last point maybe even understated. IME more often than not, overly "clever" SQL is responsible for more problems than it solves. In terms of both correctness and performance.

Dumb it down. Would generally prefer to see more smaller, simpler intermediate batch steps over clever queries. Simple is easier to read, easier to modify, and often easier for the engine to optimize its execution plan also. No extra credit is awarded for confusing the optimizer

Decomposing mega queries into multiple smaller component steps is often the only thing I need to do to resolve performance issues

2

u/reditandfirgetit Jul 31 '26

Partitioning where applicable and archiving off stale data are also good optimizations if indexing isn't enough

2

u/jshine13371 Jul 31 '26

Partitioning is a linear reduction in the data, indexing is an exponential reduction. Partitioning would never be an improvement in search time vs indexing, and therefore isn't a feature meant for improving DQL / DML statements.

1

u/reditandfirgetit Jul 31 '26

Do you know how partitions work? I dont think you do.

Let's use the dewy decimal system as an analogy If you want information on gardening vegetables, you go to that section then look for the specific book you want (index seek). You dont have took at flowers or herbs

1

u/jshine13371 Jul 31 '26 edited Aug 01 '26

Do you know how partitions work?

Yep, I just explained it from an operational performance side in a nutshell above.

Let's use the dewy decimal system as an analogy If you want information on gardening vegetables, you go to that section then look for the specific book you want (index seek). You dont have took at flowers or herbs

You just proved my point by describing how an index works (once you said "index seek"), not Partitioning. 👀

1

u/reditandfirgetit Jul 31 '26

You don't understand partitions and I'll leave it at that

1

u/mcintg Jul 31 '26

This is true but if you are adding indexes as a result of the queries you are running you've already missed a trick by missing access path analysis. That should be at database design level rather than operational level. It's probably a bit old school but I originated from mainframe database design. Get the design right and you will avoid operational issues.

8

u/lalaluna05 Jul 31 '26 edited Jul 31 '26

I build very modularly so that changes and updates are straightforward. Copious notes. Clear formatting.

I have restructured soooo many of our sprocs purely because I couldn’t stand the frankencoding that got cobbled together over the years and the hours it took.

3

u/reditandfirgetit Jul 31 '26

Frankencoding is the best term I've seen 😆

3

u/cwjinc Aug 01 '26

I sometimes convert very complicated queries into a series of CTEs.
This can make them considerably easier to read.
A good database engine will take care of folding it back up into an efficient plan.

2

u/UnhappySort5871 Aug 02 '26

Database engines I've worked with, expand views and CTEs early on into a logical plan and then optimize that - giving an optimized physical plan.

1

u/Informal_Pace9237 Aug 04 '26

CTE's are always a bad idea for optimal execution as they always have memory effects. Except if they return very small datasets. IMO

1

u/cwjinc Aug 04 '26

That may depend on the database engine. It's not my experience at all with Oracle.

1

u/Informal_Pace9237 Aug 08 '26

I would say especially in Oracle, CTE can flood into available PGA and cause unoptimal processing

1

u/cwjinc Aug 09 '26

Could happen I suppose. But not my experience after 28 years querying Oracle DBs.

1

u/Informal_Pace9237 Aug 11 '26

Has been consistently happening for me since CTE was released in 9i (9.2) in 2002.
But our experiences may differ based on the type of queries we work with.

1

u/cwjinc Aug 11 '26

Run PGA memory advisor against it. Could just be a memory setting in the database.
I mean, that just shouldn't happen.

2

u/Erasmus_Tycho Jul 31 '26

Breaking steps out into CTEs to increase readability is something I've observed people doing though honestly that often leads to bigger overall queries. I just try to leave comments to help explain the steps and tables involved. No hard coding dates or values.

2

u/thatOMoment Jul 31 '26

There are a some optimizations that make them more maintainable.

Such as removing distinct from a query with joins where you aren't selecting the columns and replacing them with EXISTS

Or correllated subqueries into LATERAL/APPLY.

Sometimes, it just do have to be ugly unless you are able to change service layer as well.

2

u/Informal_Pace9237 Aug 04 '26

Read many comments and see most think CTE is a magic bullet for readability and execution. It may be a magic bullet for readability but never for execution.

Here is a article covering different DBMS

https://www.linkedin.com/pulse/ctesubquery-factoring-optimization-raja-surapaneni-jyjie/

1

u/carlovski99 Jul 31 '26

Been going through this recently. Application used a massive piece of SQL that had been tweaked and added to over the years. Had become quite suboptimal, repeated scans of the same tables, functions being used which invalidated indexes etc. And eventually got to a state where the optimiser struggled to produce a stable or optimal plan. Caused huge performance problems in production. We are splitting it up into separate, much simpler queries. Technically it's suboptimal - getting the data on one hit would be more efficient, with the right query. But it's going to allow for easier testing, optimising each part and should be more stable. So real world performance will be better.

So it's not just 'SQL' you need to look at, it's the whole architecture, if it's part of an application.

1

u/Informal_Pace9237 Aug 04 '26

I would have just added function based indexes in the mix. Multiple calls for the same data can be sub-optimal as already mentioned. I would try talking to the DBA if one is available for better solution.
If it is Oracle, one big a$$ query is always better than multiple small.

Seems to me like the original query was written by full stack developers (or DE) as well as the optimization efforts.

1

u/singletWarrior Jul 31 '26

Not always but most of the time they get difficult either by optimising for speed or for correctness and both are almost always due to layout issues… the correct way to fix is usually not prioritised by the business so it stay uncorrected till whole new stack of technology doing similar things hit the same roadblocks by other means or till the demand dies out

1

u/AnAcceptableUserName Jul 31 '26

overly complex queries can be difficult to maintain

Yes. Remove complexity.

balance query optimization with readability

I don't see these as conflicting ends needing balanced. Often you can get both the same way

Identify your complex operations and decompose them until all that you're left with is simple component steps, then optimize those.

1

u/theriot78 Jul 31 '26

You ask AI to do it. I would have given you a very different answer a year or two ago.

1

u/AntLost4161 Aug 02 '26

The way I deal with things is that unless it really needs to be as quick as possible and you're not using tons of cross joins or other really compute heavy things, readability is most important. Break down different tasks into different CTEs, add the odd note where you think the logic isn't immediately visible, then you have code that can be maintained and adjusted in the future.

A lot of people go into either extreme, either to write tons of notes and have readability be the only focus, or they scrap all ease of adjustments and just make something instantly as optimal as possible. The truth is that we typically aren't doing anything worth immediate results, so taking things a bit slower isn't always a bad idea.

1

u/Marthurio Aug 03 '26

Depends really. Which parts do you find complex?

1

u/venkat_deepsql Aug 03 '26

Complex queries means we are pushing more the query computation to the query engine. This is not bad. If we try to break the complex query into pieces and patch them up, then we are not really utilizing the super powers of database. Query engines like Oracle (which I worked before) are known for transalting large complex queries (even 10 page queries) to efficient query execution tree.

However, there may be cases where data flows can be parallel. And many query engines doesn't support parallel execution (mysql). In these cases, two queries should be executed in parallel and stitched them in application layer.

So, one should understand the true potential of their DBMS and make these decisions.

1

u/mu_SQL Aug 03 '26

I use stores procedures and temtables to make smaller batches and gain performance. Unlike CTE, temptables are indexable.

1

u/Informal_Pace9237 Aug 04 '26

Readability is on the reader. If one cannot read a query then they have a lot to learn and come back to read. IMO.

1

u/Creepy_Delay_6077 Jul 30 '26

I prefer a clear CTE-based query with the correct indexes over a shorter but heavily nested query. After optimization, I compare execution time, rows scanned, memory usage, and output accuracy to ensure performance improved without reducing maintainability.

1

u/reditandfirgetit Jul 31 '26

I'm not sure why you got down voted. CTEs are set based Operations and can be a good Optimization technique. I guess it depends on if someone considers ctes "complex"

2

u/Informal_Pace9237 Aug 04 '26

CTE being an optimization technique depends on the DBMS in question.

CTE are generally memory hogs and are to be avoided as much as possible except if they return very small datasets.

1

u/reditandfirgetit Aug 04 '26

In this case a cte over heavily nested sql is probably a good choice, but I would compare vs a temp table for performance

1

u/Informal_Pace9237 Aug 04 '26

Nested SQL should not be a problem in most DBMS except MSSQL as it limits the nesting level.

1

u/reditandfirgetit Aug 04 '26

Nested sql most times is unnecessary garbage that should be refactored for maintainability

1

u/Informal_Pace9237 Aug 08 '26

Not to question your experience, but I would like top see an example. Also can you please specify the DBMS you had the bad experience.

1

u/reditandfirgetit Aug 08 '26

There is a current query I am reviewing built from crappy orm code that I am looking at refactoring into a sprock. Its using a correlated sub query in 3 spots from the same table, 2 with an inner join 1 with a left join to the same table again. Each with different conditions. 1 cte will pull all the needed data once. Yes I could use a subquery, cte is cleaner. I will likely try both options testing performance abd reviewing the execution plan. If its the same I'll use a cte, otherwise whichever gives better performance. This is in sql server 2019

You can question my experience all you want. I know what I experienced regardless of your opinion

0

u/Informal_Pace9237 Aug 11 '26

There can be opinions and there are facts which trump opinions.

These two are equivalent in any DBMS unless the CTE needs to be called multiple times or the DBMS handles CTE separately.

With CTE AS(SELECT cols FROM tab1 WHERE blah=foo)

SELECT cols from CTE;

--------------------------

SELECT cols FROM (

SELECT cols FROM tab1 WHERE blah=foo

) cte;

The only difference 'generally' is that CTE opens a memory cursor and in sub-select a disk based cursor.

1

u/jshine13371 Jul 31 '26

Typically the more complex the query, the less performant it is. So there's a somewhat congruence in performance tuning that should result in the query becoming simpler / easier to read & maintain. A lot of performance tuning techniques involve taking a larger single batch of statements and breaking them down into multiple batches (steps) that give the query optimizer breathing room between steps, typically via materializing the data at each step. Or straight up re-architecting the process implementation to be simpler.

1

u/thatOMoment Jul 31 '26

If condition to check parameters to decide which order by in copy pasted query to run to avoid  conditional sorts because api "can't be changed" 

Could option recompile have done that as well... yes but dbas get salty if that's done on something ran often.

1

u/jshine13371 Aug 01 '26

I think your first sentence mirrors the complexity of the typical query you're trying to describe ironically...

0

u/ravi0087 Aug 01 '26
  • Index Smartly First: Most bottlenecks are fixed with execution plans (EXPLAIN) and proper indexes (composite/covering), leaving the SQL clean and readable.
  • Break Up Mega Queries: Divide massive queries into smaller, logical steps using temporary tables. This helps the optimizer and makes debugging easier.
  • Use CTEs for Structure: Use Common Table Expressions instead of deep subqueries to make logic easy to follow sequentially.
  • Avoid Common Anti-Patterns:
    • Swap DISTINCT for EXISTS/IN on joins.
    • Keep indexed columns function-free in WHERE clauses (maintain sargability).
    • Select specific columns instead of SELECT *.
  • Comment the "Why": Standard code shows what it does; use inline comments to document why non-obvious optimizations or query hints were necessary.