Answer the question
In order to leave comments, you need to log in
How to optimize sorting by count(*)? Create index or view?
There is a table of many-to-many related posts and keywords. When selecting posts, they need to be sorted by date and the number of related keywords. This query slows down and needs to be optimized. I have already created an index for the date field, but now I need to add a sort by count. keywords. How to do it if count(*) is calculated dynamically?
If you create a view, how convenient will it be to work with it through EF?
Answer the question
In order to leave comments, you need to log in
Make a tag_count field and write int there. Post an index. Update the count when the record changes.
After adding a field, you can update the values all at once with a single query.
Well, no less classic advice - pre-calculated tables. Those. You count your count(*) in advance at night/at lunchtime (during low load) and write to a separate table. You may have to enter a flag for the post "updated since the recalculation"
in this case, your count will be the sum of the precalculated value + the counter for posts updated since the recalculation.
They would show the query, perhaps it is easier to write it differently
. Also, what index for the date did you make ?.
What does the query plan show?.
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question