V
V
Vadim Ivanenko2015-08-25 15:29:17
SQL
Vadim Ivanenko, 2015-08-25 15:29:17

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

3 answer(s)
N
Night, 2015-08-25
@maxtm

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.

M
Max, 2015-08-26
@MaxDukov

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.

A
Artur Polozov, 2015-08-27
@Noxy

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 question

Ask a Question

731 491 924 answers to any question