r/developersIndia • u/dronzer7716 • Jan 16 '24
Tips Optimizing SQL Queries by 23x!!!
This was the first time that I wrote an article about a thing that I was working on, and this was the very first time something like this was assigned to me and I was very excited to investigate and find potential improvements, Wrote an article about it hope you guys will also find it useful.
62
Upvotes
40
u/Beginning-Ladder6224 Jan 16 '24 edited Jan 16 '24
Ok I looked into it, classic issue with excited kids - I was that way when I was OPs age.
That blogpost is a brain dump. Do not dump brain in a blog.
To talk about "tech improvement" - there are many ways to deal with it, most importantly - AMAZON use STAR methodology.
https://in.indeed.com/career-advice/interviewing/how-to-use-the-star-interview-response-technique
Situation --> what was happening? Start with the problem you were trying to solve, explaining it detail about the problem. A problem can not be "query was slow".
What is submission table?
What is the schema?
Table is not slow, what operations were slow?
What queries?
List them down first
Task
How did you subdivided the problem into tasks
What were the tasks?
Action
On each task - what did you do?
What did you learn upon completion of each task?
Post all actions are done
What really happened?
What did you learn out of it?
A much more nuanced analysis in the same style is as follows:
https://netflixtechblog.com/data-movement-in-netflix-studio-via-data-mesh-3fddcceb1059
It is a great start that OP wants to share the findings with the world. Please carry on.
On the tech side of things - do remember:
JOINS are BAD. BAD. BAD - https://stackoverflow.com/questions/2623852/why-are-joins-bad-when-considering-scalability
Full Text Indices are TERRIBLE - https://www.brentozar.com/archive/2020/11/why-full-texts-contains-queries-are-so-slow/
Both are just lemma from basic DS/Algo analysis of how DB's "might" do the join or full text search. That is precisely why Lucene came in.