# Very slow query

**URL:** <https://discuss.elastic.co/t/very-slow-query/123414>\
**Category:** Elasticsearch\
**Created:** [March 11, 2018, 6:37am UTC](https://discuss.elastic.co/t/very-slow-query/123414 "2018-03-11T06:37:32Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![sasankvemuri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sasankvemuri/32/28650_2.png) [@sasankvemuri](https://discuss.elastic.co/u/sasankvemuri)\
**Post date:** [March 11, 2018, 6:37am UTC](https://discuss.elastic.co/t/very-slow-query/123414/1 "2018-03-11T06:37:32Z")

</div>

I have been trying to search and retrieve results based on 3 user input values and find the closest value that satisfies these three conditions. The doc has about 30 million rows and the query takes about 10 seconds to execute.

```auto
def sgrna(factor, efficiency, specificity, chr):
    es = Elasticsearch()
    minimum = min(factor)
    request = []
    min(factor)
    for i in range(len(chr)):
        req_head = {'index': 'lab', 'type': 'sg_rna'}
        req_body = {
                      "query": {
                        "bool": {
                          "must": [
                            {
                              "term": {
                                "BS_CHR": chr[i]
                              }
                            },
                            {
                              "range": {
                                "Efficiency": {
                                  "gte": int(efficiency)
                                }
                              }
                            },
                            {
                              "range": {
                                "Specificity": {
                                  "gte": int(specificity)
                                }
                              }
                            } ]
                        }
                      }
                    ,
                            "sort" : {
                                "_script" : {
                                    "type" : "number",
                                    "script" : {
                                        "lang": "painless",
                                        "params": {
                                      "factor": int(factor[i])
                                    },
                                        "inline": "def cur = 0; cur = (params.factor - doc['BS_START'].value); if (cur < 0) { cur = cur * -1 } else { cur = cur}" },
                                    "order" : "asc"
                                }
                            }

                        }
        request.extend([req_head, req_body])
    resp = es.msearch(body=request)
    print(resp)
    response = []
    for i in range(len(resp["responses"])):
        response.append(resp['responses'][i]['hits']["hits"][0]["_source"])
    return response

```

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [March 11, 2018, 8:17am UTC](https://discuss.elastic.co/t/very-slow-query/123414/2 "2018-03-11T08:17:57Z")

</div>

> The code formatting is bugged here so external code link

WDYM?

Use `</>` icon as explained in [this guide](https://discuss.elastic.co/t/about-the-elasticsearch-category/21) and not the citation button.

Or use markdown style like:

````
```
CODE
```

````

---

<div class="post-metadata">

**Author:** ![sasankvemuri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sasankvemuri/32/28650_2.png) [@sasankvemuri](https://discuss.elastic.co/u/sasankvemuri)\
**Post date:** [March 11, 2018, 6:36pm UTC](https://discuss.elastic.co/t/very-slow-query/123414/3 "2018-03-11T18:36:14Z")

</div>

I meant when I was on my laptop when I posted it. Not the website system (my bad I didn't explain) however the problem is the query runtime. I'm wondering if there's any better way to speed up the search?

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [March 11, 2018, 6:59pm UTC](https://discuss.elastic.co/t/very-slow-query/123414/4 "2018-03-11T18:59:43Z")

</div>

I edited your post.

What is the `took` that elasticsearch is giving back?  
What is the `took` when you remove the painless script?

BTW I'd move to a `filter` instead of `must` all your `term` and `range` queries.

---

<div class="post-metadata">

**Author:** ![sasankvemuri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sasankvemuri/32/28650_2.png) [@sasankvemuri](https://discuss.elastic.co/u/sasankvemuri)\
**Post date:** [March 11, 2018, 9:37pm UTC](https://discuss.elastic.co/t/very-slow-query/123414/5 "2018-03-11T21:37:09Z")

</div>

I assume you mean time and with the painless script it takes about 10 seconds to go through 100 iterations and without painless script there is no option that I could find in ES that will find the nearest number in an index to a given input.  
I initially used filter, however, the filter was not being specific in retrieving range values and I didnt get desired result  
for example when I gave "Specificity" should be greater than 50 it was not reflected in the output because the sort script took over the preference and hence I had to add everything to must.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [March 11, 2018, 10:12pm UTC](https://discuss.elastic.co/t/very-slow-query/123414/6 "2018-03-11T22:12:14Z")

</div>

I meant `took` field in elasticsearch response.

About using must/filter that should not change in the way you describe I think.

Could you provide a full recreation script as described in [About the Elasticsearch category](https://discuss.elastic.co/t/about-the-elasticsearch-category/21). It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

---

<div class="post-metadata">

**Author:** ![sasankvemuri](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sasankvemuri/32/28650_2.png) [@sasankvemuri](https://discuss.elastic.co/u/sasankvemuri)\
**Post date:** [March 12, 2018, 2:07am UTC](https://discuss.elastic.co/t/very-slow-query/123414/7 "2018-03-12T02:07:39Z")

</div>

This is a simplified version of the query. I basically want one field to match and then I want the matched results to return based on the nearest value from user input. In this case, user input is the "factor" which I have assigned under params.  
The index has about 300 million rows and it 'took': 2364 to complete one search.

```
{
    "query": {
        "term": {"CHR": "chr1"}
    },
    "sort" : {
        "_script" : {
            "type" : "number",
            "script" : {
                "lang": "painless",
                "params": {
              "factor": 100000
            },
                "inline": "def cur = 0; cur = (params.factor - doc['POS'].value); if (cur < 0) { cur = cur * -1 } else { cur = cur}" },
            "order" : "asc"
        }
    }

}
```

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [March 12, 2018, 3:34am UTC](https://discuss.elastic.co/t/very-slow-query/123414/8 "2018-03-12T03:34:16Z")

</div>

I was asking for a reproduction for the problem you asked before about the bool must vs bool filter part.

Also can you please give me what the `took` value is with and without the sort part?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 9, 2018, 3:34am UTC](https://discuss.elastic.co/t/very-slow-query/123414/9 "2018-04-09T03:34:20Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.