Loads of over-engineering decisions would be avoided if devs understood how to read EXPLAIN/ANALYZE results and do the proper indexing/query optimization.
Log queries, filter our the ones that are very frequent or take loads of time to execute, cache the frequent ones, optimize the fat ones, do this systematically and your system will be healthier.
Things that help massively from my experience:
- APM
- slow query log
- DB read/write replicas
- partitioning and sharding
Simply understanding how to read explain output can be quite a task in itself though, databases are a whole other thing, especially if you barely do any SQL yourself.
Tools like https://explainmysql.com that make it clearer what you actually need to optimise are an easier system for Devs with enough database knowledge to set stuff up, but not enough to understand how it's used.
I assume someone's already working on an AI system that takes schema and logs and returns the SQL needed to magically improve things. Not sure I'd trust that, but I'd bet many companies would rather use that then get a full DBA.
Understanding explain output is usually very simple. 1 Look for any occurrence of “table scan”. 2. Add index on those queried fields or limit the query by filtering on another already indexed field.
This should unclog the most low hanging fruit. Then there is of course more advanced scenarios, especially with joins.
That’s not to say that the UX for explaining (hah) this doesn’t have a lot of room for improvement.
Do you know of any good resources to understand sql explain plan. In my current project, we are facing a lot of issues related to query performance on MS SQL server. Do we need to always specify index hint with queries. Sometimes index exists but query does not seem to be using the index. I am thinking using sql execution plan could help us understand this issue better. tia.
Can't even count how many "next Gen architecture" sessions I've been at which certainly could've been replaced with due diligence on the current implementation.
You don't fix bad coding with a new architecture. That just puts the problem off by some time.
Log queries, filter our the ones that are very frequent or take loads of time to execute, cache the frequent ones, optimize the fat ones, do this systematically and your system will be healthier.
Things that help massively from my experience: - APM - slow query log - DB read/write replicas - partitioning and sharding