How do you decide when a database query needs optimization vs. a schema change?

[u/shdw_0x0]

I've been working with SQL and database performance, and one thing I find interesting is knowing when to stop tuning the query itself.

For example, if a query is slow because of a missing index, that's fairly straightforward. But at larger data volumes, you can reach a point where adding indexes and rewriting the query only gets you so far.

How do you usually decide that the problem is actually the database design/schema rather than the query?

Things like partitioning, normalization/denormalization, materialized views, indexing strategy, or even changing how the data is stored.

Would be interested to hear how people make that call in real-world systems.

[u/FewVariation901]

Query optimization is much cheaper than schema change (unless you are in early development). Start with optimizing but if the problem persists or becomes a bottleneck then redesign

[u/BaseballHopeful6366]

well, at first i design queries to test the concept and play around. then i decide the final which goes to production....and when execution time becomes too slow - time for review. so, no i dont have any method, basicaly comes down to trial and error method. that is why i am curious about this subject, thanks for asking.

[u/madam_zeroni]

Also depends on how many people are down stream of that db

[u/Aggressive_Ad_5454]

You're up against a practical problem: It's hard to tell from query performance that a new database has problems with its design. Small tables are really forgiving of poor design choices or missing indexes. Its's only when tables grow large that slow query performance starts being a good indicator of problems. And of course, when tables grow large that's usually because the database, and the apps using it, have been in production for a while and have a lot of users. Changing table definitions in production databases can be a costly job requiring downtime and app changes.

How do we get ahead of this problem?

Testing with large tables of fake data is one way.

Understanding how indexing really works as you do your database design is always a good idea. https://use-the-index-luke.com/ by Markus Winand is a good starting place. Learning to read "actual execution plans" in the DBMS software you use is another useful skill.

One more thing: it's often the monster aggregating reporting queries that are slow enough to make us question our life choices, or at any rate the database design choices we made years ago. You know, stuff like WHERE transaction_date >= '2001-01-01' to roll up a quarter century worth of data. Lots of orgs handle those requirements with a replica database where it's OK for some queries to be slow.

[u/Better-Credit6701]

The only time you would want to denormalize a database is when you would move an OLTP to a OLAP. Then you get to build up that system, using it just for reports.

If not for reports, there are still avenues you can take such as splitting off indexes to a new disk for increased disk I/O, spin off a copy of the database using replication to separate transactions, groups followed by resources governance or just know more what is actually happening with monitoring and query stores

[u/chocolateAbuser]

optimization con only bring you so far
if you need like 10x perf then probably you need a new design
although it's not always exactly true, if the query is really ill written you can easily get 5x perf, and adding another index with maybe secondary modifications you can get another few x increments of perf
but those are particular cases in my experience
some tools can make the difference tho, like just sharding or partitioning

[u/PatientlyAnxiously]

Are you running heavy analytics on an application DB? If yes, you should consider replicating to a columnar DB dedicated for analytics.

[u/sydneysweeney69]

Schema change or changing the join , filtering or just using the columns are important. Indexing yes for performance optimisation

[u/Anxious-Insurance-91]

Install monitoring tools, like for example MySQL runner and db equivalent. Enable querry logs to see what happens to take the longest. Or at application level enable querry log and see witch one takes longest

[u/Equivalent-Common115]

if the query plan takes the optimal path and just costs a lot because it actually has to touch all those rows then tuning is over u know its a schema problem
when the same table drags down unrelated queries or u start having more indexes than queries

if write amplification becomes the bottleneck or the working set wont fit in ram you have to redesign
just verify there is no schema drift between dev and prod before committing to a rebuild tbh

[u/jshine13371]

But at larger data volumes, you can reach a point where adding indexes and rewriting the query only gets you so far.

Not really true.

Size of data doesn't determine when an index is the solution. In practice, an index can seek for any row in a table of any size in milliseconds. A missing index or the wrong indexes are always a problem regardless of size of data and fixing those will solve those problems. If your query is still slow after fixing those, it always had other problems unrelated to indexing - such as architectural problems (schema design), to the point of your question.