I like query optimizing related videos, The first thing I usually try is to add indexes to all columns in WHERE/ORDER BY/ GROUP BY clauses. Thanks Ben!
Nice that you share the different ideas and trials to get to the result. I personally find the data consolidation a bit of cheating in specific case. It feels not too far from creating temp table and filling it with needed records and getting millisec response times after that :)
Yeah that's a fair point about the data consolidation and it's a bit like cheating. I could have avoided this and I probably would have gotten a response time around 5s - 10s, which is above the 1s goal.
I like query optimizing related videos,
The first thing I usually try is to add indexes to all columns in WHERE/ORDER BY/ GROUP BY clauses.
Thanks Ben!
That's a pretty good place to start!
you could run a CTE which adds the reason column and then filter on this column
That's true, we could do that.
Nice that you share the different ideas and trials to get to the result. I personally find the data consolidation a bit of cheating in specific case. It feels not too far from creating temp table and filling it with needed records and getting millisec response times after that :)
Yeah that's a fair point about the data consolidation and it's a bit like cheating. I could have avoided this and I probably would have gotten a response time around 5s - 10s, which is above the 1s goal.
I wouldn't call it query optimization if you just move all time-consuming operations to Update and then directly select rows with a single condition.
I assume that adding the "reason" column isn't a good option if the business logic (definition of rules) changes in time. Is that correct?
Yeah that's a good point
so, it mean add all column to index except primary key to improve speed of query?
You could try that. I tried some indexes on other columns which helped a little.