# How do I speed up this query? **URL:** \ **Category:** Elasticsearch\ **Created:** [May 11, 2019, 5:23am UTC](https://discuss.elastic.co/t/how-do-i-speed-up-this-query/180631 "2019-05-11T05:23:26Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![rgelb](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@rgelb](https://discuss.elastic.co/u/rgelb)\ **Post date:** [May 11, 2019, 5:23am UTC](https://discuss.elastic.co/t/how-do-i-speed-up-this-query/180631/1 "2019-05-11T05:23:26Z")
I am trying to speed up a query and not sure if I am going about it the right way (being a newcomer to ES). I am trying to find an address in an index (that contains 200 million records) by it's constituent parts. And I am looking for specific values. So for instance, I have street number (1118), street name (Tower), street type (Rd) and zip code (90210). And I am trying to find the property with that address. So my query: ``` POST properties/_search { "from": 0, "query": { "bool": { "must": [ { "match": { "streetNumber": { "query": "1118" } } }, { "match": { "streetName": { "query": "Tower" } } }, { "match": { "zip": { "query": "90210" } } }, { "match": { "streetSuffix": { "query": "Rd" } } } ] } }, "size": 10 } ``` It finds the record fine, but it takes roughly half a second. Schema for fields I am searching is defined as follows: "streetName": { "type": "string", "analyzer": "citysearch\_analyzer" }, "streetNumber": { "type": "string", "analyzer": "keyword\_analyzer" }, "streetSuffix": { "type": "string", "analyzer": "keyword\_analyzer" }, "zip": { "type": "string", "index": "not\_analyzed", "doc\_values": true } Can this query be improved? ---
**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:** [May 11, 2019, 5:48am UTC](https://discuss.elastic.co/t/how-do-i-speed-up-this-query/180631/2 "2019-05-11T05:48:42Z")
What is the output of: ```auto GET /_cat/health?v GET /_cat/indices?v GET /_cat/shards?v ``` Did you try to profile the query? Could you share the response? Does it still "slow" after some runs? ---
**Author:** ![rgelb](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@rgelb](https://discuss.elastic.co/u/rgelb)\ **Post date:** [May 11, 2019, 6:50am UTC](https://discuss.elastic.co/t/how-do-i-speed-up-this-query/180631/3 "2019-05-11T06:50:07Z")
> [@dadoonet](#): > > GET /\_cat/health?v GET /\_cat/indices?v GET /\_cat/shards?v The cluster is healthy. The indices are green. And the shards started. > [@dadoonet](#): > > Did you try to profile the query? Your question reminded me that I omitted an important piece of information. I can't profile it because we are on an ancient version of Elastic (1.7.6), which doesn't seem to have a profiler from what I can tell. So the only hope here is that I screwed up the query somehow > [@dadoonet](#): > > Could you share the response? Posted [here](https://pastebin.com/njzfWLTD). > [@dadoonet](#): > > Does it still "slow" after some runs? Running the same query is inconsistent. Majority of responses will be around 400-800 ms. Then some will be like 6 to 20 ms. There is probably caching at play here that rapidly expires. But it doesn't matter - I don't run the same query over and over. The address lookups are different every time. ---
**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:** [May 11, 2019, 9:23am UTC](https://discuss.elastic.co/t/how-do-i-speed-up-this-query/180631/4 "2019-05-11T09:23:37Z")
> [@rgelb](#): > > The cluster is healthy. The indices are green. And the shards started. That was not my question. I was asking for the output of those commands. > [@rgelb](#): > > we are on an ancient version of Elastic (1.7.6) A lot, like A LOT, of improvements have been done over the past years. You should really upgrade to 7.0. Specifically if you don't need the exact total number of hits you can benefit from many improvements. Also a newer JVM can help. > [@rgelb](#): > > There is probably caching at play here that rapidly expires. But it doesn't matter - I don't run the same query over and over. The address lookups are different every time. It does matter a lot even if you are searching for other terms. BTW what kind of hardware do you have? Ssd drives? ---
**Author:** ![rgelb](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@rgelb](https://discuss.elastic.co/u/rgelb)\ **Post date:** [May 11, 2019, 4:59pm UTC](https://discuss.elastic.co/t/how-do-i-speed-up-this-query/180631/5 "2019-05-11T16:59:46Z")
> [@dadoonet](#): > > I was asking for the output of those commands. [GET /\_cat/health?v](https://pastebin.com/u9sQVV8x) [GET /\_cat/indices?v](https://pastebin.com/it0eqbWe) [GET /\_cat/shards?v](https://pastebin.com/it0eqbWe) > [@dadoonet](#): > > upgrade to 7.0 Upgrade to 6.x is ongoing, but very slowly. Meanwhile I got to deal with what I have. > [@dadoonet](#): > > BTW what kind of hardware do you have? Ssd drives? I am not sure. I'll have to follow up on Monday. Here is the index [definition](https://pastebin.com/CR1gZejz). ---
**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:** [May 13, 2019, 10:34pm UTC](https://discuss.elastic.co/t/how-do-i-speed-up-this-query/180631/6 "2019-05-13T22:34:29Z")
I don't see an index named `properties`. Is it an alias? ---
**Author:** ![rgelb](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@rgelb](https://discuss.elastic.co/u/rgelb)\ **Post date:** [May 13, 2019, 11:25pm UTC](https://discuss.elastic.co/t/how-do-i-speed-up-this-query/180631/7 "2019-05-13T23:25:30Z")
Yes. Aliased to properties\_web04\_20180118. ---
**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:** [May 14, 2019, 7:56am UTC](https://discuss.elastic.co/t/how-do-i-speed-up-this-query/180631/8 "2019-05-14T07:56:06Z")
> [@rgelb](#): > > I am not sure. I'll have to follow up on Monday. Did you get the answer? ---
**Author:** ![rgelb](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@rgelb](https://discuss.elastic.co/u/rgelb)\ **Post date:** [May 14, 2019, 10:55pm UTC](https://discuss.elastic.co/t/how-do-i-speed-up-this-query/180631/9 "2019-05-14T22:55:59Z")
> [@dadoonet](#): > > Did you get the answer? 3 boxes. Each one has 252GB of RAM. And each one has 2 CPUs. Which are 8 cores each and then each core is hyperthreaded giving it poor man's 32 cores per box. Each box has 252GB of RAM, but the usage is around 30-40GB. The hard drives are all SSD. ---
**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:** [May 15, 2019, 5:12am UTC](https://discuss.elastic.co/t/how-do-i-speed-up-this-query/180631/10 "2019-05-15T05:12:58Z")
As you have plenty of resources on your machines, would it be possible to start a 7.0.1 cluster on the same hardware, use reindex from remote to read from the 1.7 cluster? Also maybe running hot threads API while a "long" query is running could tell something? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [June 12, 2019, 5:15am UTC](https://discuss.elastic.co/t/how-do-i-speed-up-this-query/180631/11 "2019-06-12T05:15:34Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.