# Filtered wildcard query **URL:** \ **Category:** Elasticsearch\ **Created:** [August 7, 2018, 7:35am UTC](https://discuss.elastic.co/t/filtered-wildcard-query/143259 "2018-08-07T07:35:32Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![cleaner](https://avatars.discourse-cdn.com/v4/letter/c/5e9695/32.png) [@cleaner](https://discuss.elastic.co/u/cleaner)\ **Post date:** [August 7, 2018, 7:35am UTC](https://discuss.elastic.co/t/filtered-wildcard-query/143259/1 "2018-08-07T07:35:32Z")
Hi, I need your help/advice to increase performance of wildcard query. The same question was here [Performance of filtered wildcard queries](https://discuss.elastic.co/t/performance-of-filtered-wildcard-queries/133841/1) , but is closed now. We have to use wildcard for data that is filtered by customer id. So data for applying wildcard is really small, about 200-300 records and should not be big deal for ES. But time it takes is about 5-10 seconds, while just filtering by customerId is less than second. What can we do to increase performance? I need to run query like this one: `SELECT * FROM Transactions WHERE (creditCusomerId = 123 OR debitCustomerId=123) AND search_field LIKE '%FOO%'` Here is ES query: ```auto { "query": { "constant_score": { "filter": { "bool": { "must": [ { "bool": { "should": [ { "term": { "payload.creditCustomerId": 123 } }, { "term": { "payload.debitCustomerId": 123 } } ] } }, { "bool": { "should": [ { "wildcard": { "search_field": "*FOO*" //type: KEYWORD } } ] } } ] } } } } } ``` Thanks ---
**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:** [August 7, 2018, 9:52am UTC](https://discuss.elastic.co/t/filtered-wildcard-query/143259/2 "2018-08-07T09:52:34Z")
1st of all: don't use wildcard queries. Then try something more like: ```auto GET index/_search { "query": { "bool": { "filter": [ { "bool": { "should": [ { "term": { "payload.creditCustomerId": 123 } }, { "term": { "payload.debitCustomerId": 123 } } ] } } ], "must": [ { "wildcard": { "search_field": "*FOO*" } } ] } } } ``` ---
**Author:** ![cleaner](https://avatars.discourse-cdn.com/v4/letter/c/5e9695/32.png) [@cleaner](https://discuss.elastic.co/u/cleaner)\ **Post date:** [August 8, 2018, 7:35am UTC](https://discuss.elastic.co/t/filtered-wildcard-query/143259/3 "2018-08-08T07:35:16Z")
Thanks David, We know wildcard is expensive feature. But requirements forces us to search for a substring. On other hand we expected that wildcard will be applied to tiny subset, about 300 docs. We tried post\_filter, results were just slightly better I used Profile API and noticed that most time is spent on build\_scorer. In our case we use **constant\_score**. Why does build\_scorer take so much time, when we do not need it? ``` { "type": "MultiTermQueryConstantScoreWrapper", "description": "search_field:*FOO*", "time_in_nanos": 10514827001, "breakdown": { "score": 0, "build_scorer_count": 15, "match_count": 0, "create_weight": 1535, "next_doc": 0, "match": 0, "create_weight_count": 1, "next_doc_count": 0, "score_count": 0, "build_scorer": 10514821338, "advance": 4093, "advance_count": 19 } } ``` ---
**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:** [August 8, 2018, 7:42am UTC](https://discuss.elastic.co/t/filtered-wildcard-query/143259/4 "2018-08-08T07:42:42Z")
> [@cleaner](#): > > But requirements forces us to search for a substring. That's why I'd encourage you looking at ngrams instead. You'll pay the price at index time (disk space wise and index time) but don't pay it at search time. Did you try my query proposal? Could you also try this one? ```auto GET index/_search { "query": { "bool": { "filter": [ { "bool": { "should": [ { "term": { "payload.creditCustomerId": 123 } }, { "term": { "payload.debitCustomerId": 123 } } ] } }, { "wildcard": { "search_field": "*FOO*" } } ] } } } ``` BTW which version are you using? ---
**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:** [August 8, 2018, 7:44am UTC](https://discuss.elastic.co/t/filtered-wildcard-query/143259/5 "2018-08-08T07:44:45Z")
BTW I forgot, you can use the Rescore API to first filter by ID which should be fast then apply the wildcard in the rescore part. See [https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-rescore.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-rescore.html) ---
**Author:** ![cleaner](https://avatars.discourse-cdn.com/v4/letter/c/5e9695/32.png) [@cleaner](https://discuss.elastic.co/u/cleaner)\ **Post date:** [August 8, 2018, 8:33am UTC](https://discuss.elastic.co/t/filtered-wildcard-query/143259/6 "2018-08-08T08:33:30Z")
ES version is 6.3.1 Yes, I tried your query - same results. We have not experience yet with ngrams, not quite understand how it searches longer substrings than ngram length. If you know some good article, please post. Thanks for Rescore API, will try it also ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [September 5, 2018, 8:33am UTC](https://discuss.elastic.co/t/filtered-wildcard-query/143259/7 "2018-09-05T08:33:52Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.