LINQ and big table, how to solve Count problem?

Question

C++ / C#

Can't solve problem with collection size calculation. There is a table on the site with filters, 10 records are displayed there.

data = dc.books.OrderBy(x => x.id).Where(x =>
                                     (market_id == 0 || x.market_id != 0 && x.market_id == market_id)
                                     && (name == null || x.name != null && (x.name.StartsWith(name)))
                                     && (author == null || x.author != null && (x.author.StartsWith(author)))
                                     ).Skip(param.Start).Take(10).ToList();

Everything is fast and works well. Well, I need to calculate the size of the resulting collection.

count = db.books.Count(x =>
                    (market_id == 0 || x.market_id != 0 && x.market_id == market_id)
                    && (name == null || x.name != null && (x.name.StartsWith(name)))

Under one condition, everything works quickly, but as soon as I add two other conditions, everything hangs up. Base on Mysql ~ 500,000. And in general, what is the best way to solve this problem with large tables? How can I solve this problem, please help.

Answer 1

Can AsNoTracking speed up query execution?

count = db.books.AsNoTracking().Count(x =>
                    (market_id == 0 || x.market_id != 0 && x.market_id == market_id)
                    && (name == null || x.name != null && (x.name.StartsWith(name)))