Create speed up search query elasticsearch

Question

I needs to change the current code searching because it looks too slow one search takes about 15 seconds.

We are looking for the parameters

?uids=1,2

I have this code:

private NativeSearchQueryBuilder getSearchQuery(AuctionIndexSearchParams searchParams, Pageable pageable) {
        final List<FilterBuilder> filters = Lists.newArrayList();
        final NativeSearchQueryBuilder searchQuery = new NativeSearchQueryBuilder().withQuery(matchAllQuery());

        Optional.ofNullable(searchParams.getCategoryId()).ifPresent(v -> filters.add(boolFilter().must(termFilter("cat", v))));
        Optional.ofNullable(searchParams.getCurrency()).ifPresent(v -> filters.add(boolFilter().must(termFilter("curr", v))));
        Optional.ofNullable(searchParams.getTreeCategoryId()).ifPresent(v -> filters.add(boolFilter().must(termFilter("tcat", v))));
        Optional.ofNullable(searchParams.getUid()).ifPresent(v -> filters.add(boolFilter().must(termFilter("uid", v))));
        final BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();

        //access for many uids
        if (searchParams.getUids() != null) {
            Optional.ofNullable(searchParams.getUids().split(",")).ifPresent(v -> {
            filters.add(boolFilter().must(termsFilter("uid", v)));
        });
        }
        //access for many categories
        if (searchParams.getCategories() != null) {
            for(String category : searchParams.getCategories().split(",")){
                 Optional.ofNullable(searchParams.getCategories().split(",")).ifPresent(v -> {
            filters.add(boolFilter().must(termsFilter("cat", v)));
        });
             }
        }

        if (searchParams.getItemId() != null) {
            boolQueryBuilder.must(queryStringQuery(searchParams.getItemId()).field("_id"));
        }

        if (Optional.ofNullable(searchParams.getTitle()).isPresent()) {
            boolQueryBuilder.must(queryStringQuery(searchParams.getTitle()).analyzeWildcard(true).field("title"));
        }

        if (Optional.ofNullable(searchParams.getStartDateFrom()).isPresent()
                || Optional.ofNullable(searchParams.getStartDateTo()).isPresent()) {
            filters.add(rangeFilter("start_date").from(searchParams.getStartDateFrom()).to(searchParams.getStartDateTo()));
        }

        if (Optional.ofNullable(searchParams.getEndDateFrom()).isPresent()
                || Optional.ofNullable(searchParams.getEndDateTo()).isPresent()) {
            filters.add(rangeFilter("end_date").from(searchParams.getEndDateFrom()).to(searchParams.getEndDateTo()));
        }

        if (Optional.ofNullable(searchParams.getPriceFrom()).isPresent()
                || Optional.ofNullable(searchParams.getPriceTo()).isPresent()) {
            filters.add(rangeFilter("price").from(searchParams.getPriceFrom()).to(searchParams.getPriceTo()));
        }

        searchQuery.withQuery(boolQueryBuilder);

        FilterBuilder[] filterArr = new FilterBuilder[filters.size()];
        filterArr = filters.toArray(filterArr);
        searchQuery.withFilter(andFilter(filterArr));

        if (searchParams.getOrderBy() != null && searchParams.getOrderDir() != null) {
            if (searchParams.getOrderDir().toLowerCase().equals("asc")) {
                searchQuery.withSort(SortBuilders.fieldSort(searchParams.getOrderBy()).order(SortOrder.ASC));
            } else {
                searchQuery.withSort(SortBuilders.fieldSort(searchParams.getOrderBy()).order(SortOrder.DESC));
            }
        }

        if (pageable != null) {
            searchQuery.withPageable(pageable);
        }
        return searchQuery;
    }

Searching lasts 15 sec. 16k records.

We are looking for the parameters

?uids=1,2&title=xyz

Searching lasts 15 sec. 800 records

---

We are looking for the following parameters:

?uids=1,2

I change the code to(look boolQueryBuilder):

private NativeSearchQueryBuilder getSearchQuery(AuctionIndexSearchParams searchParams, Pageable pageable) {
        final List<FilterBuilder> filters = Lists.newArrayList();
        final NativeSearchQueryBuilder searchQuery = new NativeSearchQueryBuilder().withQuery(matchAllQuery());

        Optional.ofNullable(searchParams.getCategoryId()).ifPresent(v -> filters.add(boolFilter().must(termFilter("cat", v))));
        Optional.ofNullable(searchParams.getCurrency()).ifPresent(v -> filters.add(boolFilter().must(termFilter("curr", v))));
        Optional.ofNullable(searchParams.getTreeCategoryId()).ifPresent(v -> filters.add(boolFilter().must(termFilter("tcat", v))));
        Optional.ofNullable(searchParams.getUid()).ifPresent(v -> filters.add(boolFilter().must(termFilter("uid", v))));
        final BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();

        //access for many uids
        if (searchParams.getUids() != null) {
            for (String user : searchParams.getUids().split(",")) {
                boolQueryBuilder.should(queryStringQuery(user).field("uid"));
            }
        }
        //access for many categories
        if (searchParams.getCategories() != null) {
            for(String category : searchParams.getCategories().split(",")){
                 boolQueryBuilder.should(queryStringQuery(category).field("cat"));
             }
        }

        if (searchParams.getItemId() != null) {
            boolQueryBuilder.must(queryStringQuery(searchParams.getItemId()).field("_id"));
        }

        if (Optional.ofNullable(searchParams.getTitle()).isPresent()) {
            boolQueryBuilder.must(queryStringQuery(searchParams.getTitle()).analyzeWildcard(true).field("title"));
        }

        if (Optional.ofNullable(searchParams.getStartDateFrom()).isPresent()
                || Optional.ofNullable(searchParams.getStartDateTo()).isPresent()) {
            filters.add(rangeFilter("start_date").from(searchParams.getStartDateFrom()).to(searchParams.getStartDateTo()));
        }

        if (Optional.ofNullable(searchParams.getEndDateFrom()).isPresent()
                || Optional.ofNullable(searchParams.getEndDateTo()).isPresent()) {
            filters.add(rangeFilter("end_date").from(searchParams.getEndDateFrom()).to(searchParams.getEndDateTo()));
        }

        if (Optional.ofNullable(searchParams.getPriceFrom()).isPresent()
                || Optional.ofNullable(searchParams.getPriceTo()).isPresent()) {
            filters.add(rangeFilter("price").from(searchParams.getPriceFrom()).to(searchParams.getPriceTo()));
        }

        searchQuery.withQuery(boolQueryBuilder);

        FilterBuilder[] filterArr = new FilterBuilder[filters.size()];
        filterArr = filters.toArray(filterArr);
        searchQuery.withFilter(andFilter(filterArr));

        if (searchParams.getOrderBy() != null && searchParams.getOrderDir() != null) {
            if (searchParams.getOrderDir().toLowerCase().equals("asc")) {
                searchQuery.withSort(SortBuilders.fieldSort(searchParams.getOrderBy()).order(SortOrder.ASC));
            } else {
                searchQuery.withSort(SortBuilders.fieldSort(searchParams.getOrderBy()).order(SortOrder.DESC));
            }
        }

        if (pageable != null) {
            searchQuery.withPageable(pageable);
        }
        return searchQuery;
    }

---

Searching lasts 3s. 16k records.

We are looking for the following parameters:

?uids=1,2&title=xyz

Searching lasts 3s. 3 million records.

It looks as if the query these parameters are combined using OR and returns too many results. How to change the code to find work as the creation boolQueryBuilder?

For any help I would be very grateful!

Answer 1

The way ElasticSearch's bool query works is:

• The must query, well, must match the found document
• The should queries just boost the resulting score

If must matches, but none of the shouldqueries do, still all results for must will be returned.

See https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-bool-query.html

Depending on your actual search needs, you may want to wrap your queries for queries for uid and title in another and-query: https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-and-query.html

You would then have a hierarchy like this:

{
    "bool": {
         "must": {
             "and": [
                  "query_string": { /* for title */ },
                  "query_string": { /* for uid */ }
             ]
         },
         "should": [ /* more queries to boost documents with matching fields */ ]
    }

}

Generally, I'd also question the use of a search query-query for uid. I guess a term query might be more performant.