How to optimize sorting by count(*)? Create index or view?

Question

SQL

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 1

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.

Answer 2

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.

Answer 3

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?.