I make an application that works server side and shows a table with 120,000 books.

enter image description here

Everything seems to be ok, except for speed, it suffers from this and stability throws out constantly.

Message=Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Innodb database search is carried out across the field the name of the book, this is how my controller looks.

 [ValidateInput(false)] public JsonResult DataTableGet() { var draw = Request.Form.GetValues("draw").FirstOrDefault(); var start = Request.Form.GetValues("start").FirstOrDefault(); var length = Request.Form.GetValues("length").FirstOrDefault(); //Find order columns info var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault(); var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault(); var value = Request.Form.GetValues("search[value]").FirstOrDefault(); var page_types = Request.Form.GetValues("columns[1][search][value]").FirstOrDefault(); var year = Request.Form.GetValues("columns[2][search][value]").FirstOrDefault(); var illustrations = Request.Form.GetValues("columns[3][search][value]").FirstOrDefault(); var cover_type = Request.Form.GetValues("columns[4][search][value]").FirstOrDefault(); var cover_design = Request.Form.GetValues("columns[5][search][value]").FirstOrDefault(); int pageSize = length != null ? Convert.ToInt32(length) : 0; int skip = start != null ? Convert.ToInt16(start) : 0; int recordsTotal = 0; using (books_entites dc = new books_entites()) { var v = (from a in dc.labirints select a); if (!string.IsNullOrEmpty(value)) { v = v.Where(p => p.name.ToString().ToLower().Contains(value.ToString().ToLower())); } if (!string.IsNullOrEmpty(year)) { int year_int = int.Parse(year); v = v.Where(a => a.year == year_int); } if (!string.IsNullOrEmpty(page_types)) { v = v.Where(a => a.pages_type == page_types); } if (!string.IsNullOrEmpty(illustrations)) { v = v.Where(a => a.illustrations == illustrations); } if (!string.IsNullOrEmpty(cover_type)) { v = v.Where(a => a.cover_type == cover_type); } if (!string.IsNullOrEmpty(cover_design)) { v = v.Where(a => a.cover_design == cover_design); } if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir))) { v = v.OrderBy(sortColumn + " " + sortColumnDir); } recordsTotal = v.Count(); var data = v.Skip(skip).Take(pageSize).ToList(); return Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data }, JsonRequestBehavior.AllowGet); }

The page loads quickly scrolling too normal. But the trouble is when I enter the text search field.

Sometimes it looks for 40-50 seconds, although the field

title of the book

indexed. Two days I can not sleep and try to understand why.

In databases I am not strong. But found what query comes when searching on Mysql

 SELECT `GroupBy1`.`A1` AS `C1` FROM (SELECT COUNT(1) AS `A1` FROM `labirint` AS `Extent1` WHERE (LOCATE(LOWER(CASE WHEN ('Французско-русский' IS NULL) THEN ('') ELSE ('Французско-русский') END), LOWER(CASE WHEN (`Extent1`.`name` IS NULL) THEN ('') ELSE (`Extent1`.`name`) END))) > 0) AS `GroupBy1

I entered him in the admin, he also thought about 40 seconds, but answered. Where do I dig?

I sin on the Entity Framework at this place.

  if (!string.IsNullOrEmpty(value)) { v = v.Where(p => p.name.ToString().ToLower().Contains(value.ToString().ToLower())); }

I watch Mysql and see this. enter image description here

But it can not be that such a well-known orm worked so poorly.

    1 answer 1

    it cannot be that such a well-known orm works so poorly.

    Of course not. MySQL has nothing to do with it. He was told he does. There is no alternative to a full scan of the table (well, or, if lucky, the index as a table). And the index cannot speed up the search, because LOCATE cannot use any index, only fullscan. So formally, you need to make a claim to the framework and the implementation of the Contains method in it. But nothing can be presented to him either - simply because what should be broadcast differently? in LIKE '% text%' ? horseradish radish ...

    In order to really speed up the search, you must either use a full-text search on this field, or use external search mechanisms like the Sphinx or the Elastic. But how personally it is implemented, if at all implemented, in the framework used, I personally have no idea.