# Query response times

**URL:** <https://discuss.elastic.co/t/query-response-times/10480>\
**Category:** Elasticsearch\
**Created:** [January 23, 2013, 7:21pm UTC](https://discuss.elastic.co/t/query-response-times/10480 "2013-01-23T19:21:49Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![shift](https://avatars.discourse-cdn.com/v4/letter/s/2bfe46/32.png) [@shift](https://discuss.elastic.co/u/shift)\
**Post date:** [January 23, 2013, 7:21pm UTC](https://discuss.elastic.co/t/query-response-times/10480/1 "2013-01-23T19:21:49Z")

</div>

Searches can be very slow, especially when I need to search a large field  
like @message. It can take up to 45 seconds. The time improves if I do  
not need to use asterisks, it'll reduce from 45 seconds to 9 seconds. If I  
select which index to search, it'll reduce to 0.51 seconds (no asterisks),  
or 12.9 seconds (with asterisks), times vary. Unfortunately, some users  
will search for generic strings that require us to append asterisks to find  
results.

I am using hourly indexes, keeping 24 hours total (but hope to increase  
this to 7 days eventually), at peak load an index can contain 69,308,904  
documents, with a size of 33GB (or 66GB replicated).

What can I do to improve these queries? I need to address the need for  
using asterisks and route the user to the appropriate index if possible.  
Should I try index routing? Are there any good example templates?

Here is an example @message:  
A|aBCdef|Jan 22 08:32:26 2013|log.sample.app.call.SampleSvr|12345|node|  
123456|bar |CodeName.cpp|123|\*\*\*\*\* START OF A LONG MESSAGE \*\*\*\*\*|12345.0123

Here is an example query:

{  
"query": {  
"bool": {  
"must": [  
{  
"query\_string": {  
"default\_field": "@message",  
"query": "_SampleSvr_"  
}  
}  
],  
"must\_not": [],  
"should": []  
}  
},  
"from": 0,  
"size": 50,  
"sort": [],  
"facets": { }  
}

--

---

<div class="post-metadata">

**Author:** ![egaumer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/egaumer/32/2365_2.png) [@egaumer](https://discuss.elastic.co/u/egaumer)\
**Post date:** [January 23, 2013, 7:47pm UTC](https://discuss.elastic.co/t/query-response-times/10480/2 "2013-01-23T19:47:53Z")

</div>

Leading wildcards are expensive.

In your example query above, you shouldn't need to use wildcards if you've  
properly tokenized the input.

log.sample.app.call.SampleSvr|12345|node|

Would be tokenized as...

[log, sample, app, call, samplesvr, 12345, node]

With proper tokenization, punctuation would cause the token to be split  
allowing you to simply search for "simplesvr" without the wildcards. I  
would focus on this before you descend into routing semantics.

On Wednesday, January 23, 2013 2:21:49 PM UTC-5, shift wrote:

> Searches can be very slow, especially when I need to search a large field  
> like @message. It can take up to 45 seconds. The time improves if I do  
> not need to use asterisks, it'll reduce from 45 seconds to 9 seconds. If I  
> select which index to search, it'll reduce to 0.51 seconds (no asterisks),  
> or 12.9 seconds (with asterisks), times vary. Unfortunately, some users  
> will search for generic strings that require us to append asterisks to find  
> results.
> 
> I am using hourly indexes, keeping 24 hours total (but hope to increase  
> this to 7 days eventually), at peak load an index can contain 69,308,904  
> documents, with a size of 33GB (or 66GB replicated).
> 
> What can I do to improve these queries? I need to address the need for  
> using asterisks and route the user to the appropriate index if possible.  
> Should I try index routing? Are there any good example templates?
> 
> Here is an example @message:  
> A|aBCdef|Jan 22 08:32:26 2013|log.sample.app.call.SampleSvr|12345|node|  
> 123456|bar |CodeName.cpp|123|\*\*\*\*\* START OF A LONG MESSAGE \*\*\*\*\*|12345.0123
> 
> Here is an example query:
> 
> {  
> "query": {  
> "bool": {  
> "must": [  
> {  
> "query\_string": {  
> "default\_field": "@message",  
> "query": "_SampleSvr_"  
> }  
> }  
> ],  
> "must\_not": ,  
> "should":   
> }  
> },  
> "from": 0,  
> "size": 50,  
> "sort": ,  
> "facets": { }  
> }

--

---

<div class="post-metadata">

**Author:** ![shift](https://avatars.discourse-cdn.com/v4/letter/s/2bfe46/32.png) [@shift](https://discuss.elastic.co/u/shift)\
**Post date:** [January 23, 2013, 10:04pm UTC](https://discuss.elastic.co/t/query-response-times/10480/3 "2013-01-23T22:04:08Z")

</div>

Thanks, I tokenized it with a nonword pattern and it's working great  
without wildcards. I will continue to research tokenizing, but this is a  
vast improvement already.

i.e. -

"settings" : {  
"index.analysis.analyzer.nonword.type" : "pattern",  
"index.analysis.analyzer.nonword.pattern" : "[^\w]+"  
},  
"mappings" : {  
"tcp" : {  
"properties" : {  
"@message" : {  
"type" : "string",  
"analyzer" : "nonword"  
}  
}  
}  
}

On Wednesday, January 23, 2013 2:21:49 PM UTC-5, shift wrote:

> Searches can be very slow, especially when I need to search a large field  
> like @message. It can take up to 45 seconds. The time improves if I do  
> not need to use asterisks, it'll reduce from 45 seconds to 9 seconds. If I  
> select which index to search, it'll reduce to 0.51 seconds (no asterisks),  
> or 12.9 seconds (with asterisks), times vary. Unfortunately, some users  
> will search for generic strings that require us to append asterisks to find  
> results.
> 
> I am using hourly indexes, keeping 24 hours total (but hope to increase  
> this to 7 days eventually), at peak load an index can contain 69,308,904  
> documents, with a size of 33GB (or 66GB replicated).
> 
> What can I do to improve these queries? I need to address the need for  
> using asterisks and route the user to the appropriate index if possible.  
> Should I try index routing? Are there any good example templates?
> 
> Here is an example @message:  
> A|aBCdef|Jan 22 08:32:26 2013|log.sample.app.call.SampleSvr|12345|node|  
> 123456|bar |CodeName.cpp|123|\*\*\*\*\* START OF A LONG MESSAGE \*\*\*\*\*|12345.0123
> 
> Here is an example query:
> 
> {  
> "query": {  
> "bool": {  
> "must": [  
> {  
> "query\_string": {  
> "default\_field": "@message",  
> "query": "_SampleSvr_"  
> }  
> }  
> ],  
> "must\_not": ,  
> "should":   
> }  
> },  
> "from": 0,  
> "size": 50,  
> "sort": ,  
> "facets": { }  
> }

--

---

<div class="post-metadata">

**Author:** ![Justin\_Treher](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/justin_treher/32/45243_2.png) [@Justin\_Treher](https://discuss.elastic.co/u/Justin_Treher)\
**Post date:** [January 24, 2013, 1:37am UTC](https://discuss.elastic.co/t/query-response-times/10480/4 "2013-01-24T01:37:11Z")

</div>

Shift - Leading wildcards are index killers period, not just in ES. Try  
putting a leading wildcard against a regular database table with a few  
million rows.

On Wednesday, January 23, 2013 5:04:08 PM UTC-5, shift wrote:

> Thanks, I tokenized it with a nonword pattern and it's working great  
> without wildcards. I will continue to research tokenizing, but this is a  
> vast improvement already.
> 
> i.e. -
> 
> "settings" : {  
> "index.analysis.analyzer.nonword.type" : "pattern",  
> "index.analysis.analyzer.nonword.pattern" : "[^\w]+"  
> },  
> "mappings" : {  
> "tcp" : {  
> "properties" : {  
> "@message" : {  
> "type" : "string",  
> "analyzer" : "nonword"  
> }  
> }  
> }  
> }
> 
> On Wednesday, January 23, 2013 2:21:49 PM UTC-5, shift wrote:
> 
> > Searches can be very slow, especially when I need to search a large field  
> > like @message. It can take up to 45 seconds. The time improves if I do  
> > not need to use asterisks, it'll reduce from 45 seconds to 9 seconds. If I  
> > select which index to search, it'll reduce to 0.51 seconds (no asterisks),  
> > or 12.9 seconds (with asterisks), times vary. Unfortunately, some users  
> > will search for generic strings that require us to append asterisks to find  
> > results.
> > 
> > I am using hourly indexes, keeping 24 hours total (but hope to increase  
> > this to 7 days eventually), at peak load an index can contain 69,308,904  
> > documents, with a size of 33GB (or 66GB replicated).
> > 
> > What can I do to improve these queries? I need to address the need for  
> > using asterisks and route the user to the appropriate index if possible.  
> > Should I try index routing? Are there any good example templates?
> > 
> > Here is an example @message:  
> > A|aBCdef|Jan 22 08:32:26 2013|log.sample.app.call.SampleSvr|12345|node|  
> > 123456|bar |CodeName.cpp|123|\*\*\*\*\* START OF A LONG MESSAGE \*\*\*\*\*|12345.0123
> > 
> > Here is an example query:
> > 
> > {  
> > "query": {  
> > "bool": {  
> > "must": [  
> > {  
> > "query\_string": {  
> > "default\_field": "@message",  
> > "query": "_SampleSvr_"  
> > }  
> > }  
> > ],  
> > "must\_not": ,  
> > "should":   
> > }  
> > },  
> > "from": 0,  
> > "size": 50,  
> > "sort": ,  
> > "facets": { }  
> > }

--

---

<div class="post-metadata">

**Author:** ![egaumer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/egaumer/32/2365_2.png) [@egaumer](https://discuss.elastic.co/u/egaumer)\
**Post date:** [January 24, 2013, 2:34am UTC](https://discuss.elastic.co/t/query-response-times/10480/5 "2013-01-24T02:34:00Z")

</div>

If you really need leading wildcards, the trick is to index each token in  
reverse order (i.e., backwards). The result is that leading wildcard  
searches become trailing wildcard searches which are more efficient.

On Wednesday, January 23, 2013 8:37:11 PM UTC-5, [jtr...@gmail.com](mailto:jtr...@gmail.com) wrote:

> Shift - Leading wildcards are index killers period, not just in ES. Try  
> putting a leading wildcard against a regular database table with a few  
> million rows.
> 
> On Wednesday, January 23, 2013 5:04:08 PM UTC-5, shift wrote:
> 
> > Thanks, I tokenized it with a nonword pattern and it's working great  
> > without wildcards. I will continue to research tokenizing, but this is a  
> > vast improvement already.
> > 
> > i.e. -
> > 
> > "settings" : {  
> > "index.analysis.analyzer.nonword.type" : "pattern",  
> > "index.analysis.analyzer.nonword.pattern" : "[^\w]+"  
> > },  
> > "mappings" : {  
> > "tcp" : {  
> > "properties" : {  
> > "@message" : {  
> > "type" : "string",  
> > "analyzer" : "nonword"  
> > }  
> > }  
> > }  
> > }
> > 
> > On Wednesday, January 23, 2013 2:21:49 PM UTC-5, shift wrote:
> > 
> > > Searches can be very slow, especially when I need to search a large  
> > > field like @message. It can take up to 45 seconds. The time improves if I  
> > > do not need to use asterisks, it'll reduce from 45 seconds to 9 seconds.  
> > > If I select which index to search, it'll reduce to 0.51 seconds (no  
> > > asterisks), or 12.9 seconds (with asterisks), times vary. Unfortunately,  
> > > some users will search for generic strings that require us to append  
> > > asterisks to find results.
> > > 
> > > I am using hourly indexes, keeping 24 hours total (but hope to increase  
> > > this to 7 days eventually), at peak load an index can contain 69,308,904  
> > > documents, with a size of 33GB (or 66GB replicated).
> > > 
> > > What can I do to improve these queries? I need to address the need for  
> > > using asterisks and route the user to the appropriate index if possible.  
> > > Should I try index routing? Are there any good example templates?
> > > 
> > > Here is an example @message:  
> > > A|aBCdef|Jan 22 08:32:26 2013|log.sample.app.call.SampleSvr|12345|node|  
> > > 123456|bar |CodeName.cpp|123|\*\*\*\*\* START OF A LONG MESSAGE \*\*\*\*\*|12345.0123
> > > 
> > > Here is an example query:
> > > 
> > > {  
> > > "query": {  
> > > "bool": {  
> > > "must": [  
> > > {  
> > > "query\_string": {  
> > > "default\_field": "@message",  
> > > "query": "_SampleSvr_"  
> > > }  
> > > }  
> > > ],  
> > > "must\_not": ,  
> > > "should":   
> > > }  
> > > },  
> > > "from": 0,  
> > > "size": 50,  
> > > "sort": ,  
> > > "facets": { }  
> > > }

--

---

<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:** [July 6, 2017, 2:54am UTC](https://discuss.elastic.co/t/query-response-times/10480/6 "2017-07-06T02:54:52Z")

</div>