# ES query performance when searching on filed with wildcard in field name

**URL:** <https://discuss.elastic.co/t/es-query-performance-when-searching-on-filed-with-wildcard-in-field-name/75658>\
**Category:** Elasticsearch\
**Created:** [February 20, 2017, 2:02am UTC](https://discuss.elastic.co/t/es-query-performance-when-searching-on-filed-with-wildcard-in-field-name/75658 "2017-02-20T02:02:58Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Youxu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/youxu/32/117375_2.png) [@Youxu](https://discuss.elastic.co/u/Youxu)\
**Post date:** [February 20, 2017, 2:02am UTC](https://discuss.elastic.co/t/es-query-performance-when-searching-on-filed-with-wildcard-in-field-name/75658/1 "2017-02-20T02:02:58Z")

</div>

I saw significant performance difference between searching on field with exact field name and searching on field with wildcard suffix i name.

Following is my mapping:

```auto
PUT /myindex
{
     "mappings": {
      "docs": {
          "Locale": {
            "type": "text",
            "fields": {
                "lowercaseraw": {
                     "type": "text",
                     "analyzer": "lowercase_keyword"
                }
            },
            "analyzer": "standard"
          },
          "Title": {
            "type": "text",
            "analyzer": "standard"
          },
          "Title_english": {
            "type": "text",
            "analyzer": "english"
          },
          "Title_french": {
            "type": "text",
            "analyzer": "french"
          },
          "Title_finnish": {
            "type": "text",
            "analyzer": "finnish"
          }
     }
}

```

_Note: There are totally around 50 Title\_ fields each of which use a specific ES language analyzer. I just list a few as example._\*

When ingesting document, I will insert the doc with TWO Title, one is for "Title", the other is for "Title\_xxx", "xxx" will be determined by the language of the doc.

For example, if the doc is english, then:

```auto
POST /myindex/docs/doc_1
{
  "Title": "Elasticsearch 5.0 is released!",
  "Title_english": "Elasticsearch 5.0 is released!",
  "Locale": "en-us"
}

```

If the doc is french, then:

```auto
POST /myindex/docs/doc_1
{
  "Title": "Elasticsearch 5.0 is released!",
  "Title_french": "Elasticsearch 5.0 is released!",
  "Locale": "fr-fr"
}

```

Similar to other languages.

So, although there are 50 around Title\_\* fields in mapping, there is always TWO Title fields has value.

Then I saw significant query performance difference when searching **english** documents with following two different query DSL:

1. 

```auto
{
  "query" : {
    "bool": {
        "must": {
          "multi_match" : {
            "query" : "elasticserach 5",
            "fields" : ["Title_english"],
            "type" : "phrase",
            "slop" : 5,
            "boost" : 10
          }
        },
      "filter" : {
        "bool" : {
          "must" : 
            {
                "match" : {
                  "Locale.lowercaseraw" : "en-us"
                }
            }
        }
      }
    }
  }
}

```

1. 

```auto
{
  "query" : {
    "bool": {
        "must": {
          "multi_match" : {
            "query" : "elasticsearch 5",
            "fields" : ["Title_*"],
            "type" : "phrase",
            "slop" : 5,
            "boost" : 10
          }
        },
      "filter" : {
        "bool" : {
          "must" : 
            {
                "match" : {
                  "Locale.lowercaseraw" : "en-us"
                }
            }
        }
      }
    }
  }
}

```

The only difference is, that the #1 DSL use "Title\_english", the #2 use "Title\ __", and please be noticed that the filter makes sure only english documents match the query, that is, only Title and Title\_english in the matched documents have value, all other Title\__ is null.

Per my rough perf testing using JMeter (200 QPS), the perf result is as follows:  
#1:  
Avg. CPU usage: 33%  
Avg. Response Time: 26 ms

#2  
Avg. CPU usage: 67%  
Avg. Response Time: 50 ms

Although #2 use "Title\_\*", the ES actually search on one field, "Title\_english", since the filter makes sure only "Title\_english" has value.

So why so big difference? Is it bad practice using wildcard suffix in search field name?

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [February 24, 2017, 10:27am UTC](https://discuss.elastic.co/t/es-query-performance-when-searching-on-filed-with-wildcard-in-field-name/75658/2 "2017-02-24T10:27:49Z")

</div>

Why? Because each doc needs to be checked to see if any of the fields begin in `Title_`, that takes time.  
It's like a full table scan in a RDBMS.

---

<div class="post-metadata">

**Author:** ![Youxu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/youxu/32/117375_2.png) [@Youxu](https://discuss.elastic.co/u/Youxu)\
**Post date:** [February 24, 2017, 10:32am UTC](https://discuss.elastic.co/t/es-query-performance-when-searching-on-filed-with-wildcard-in-field-name/75658/3 "2017-02-24T10:32:06Z")

</div>

It makes sense, but it is interesting that if Title\_xxx field value is empty, the query on ["Title", "Title\_\*"] perf is quite good. Whereas if Title\_english is not empty, query only on ["Title\_\*"] got very bad performance.  
But in two cases, only one field is searched, and all used wildcard.

---

<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:** [March 24, 2017, 10:32am UTC](https://discuss.elastic.co/t/es-query-performance-when-searching-on-filed-with-wildcard-in-field-name/75658/4 "2017-03-24T10:32:09Z")

</div>

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