Question: This is the code I have to optimize, I managed to get it to half of the starting cost only with indexes and changing the LIKE for a SUBSTRING sentence. My problem is now with the sub-query in the last line, and the SUM lines in the select, I believe I have to get rid of those by creating a new table or column but can't get it done.

```
SELECT 
C.LastName as Customer , e.LastName as SalesPerson, s.ProductID,
p.Name as ProductName, SUM( s.Quantity ) as quantity, 
SUM ( p.Price * s.Quantity ) as amount 
FROM dbo.Sales s, dbo.Customers c, dbo.Employees e, dbo.Products p 
WHERE 
 s.CustomerID = c.CustomerID and 
 s.ProductID = p.ProductID and 
 s.SalesPersonID = e.EmployeeID and
 p.Name like 'Paint%' 
GROUP BY C.LastName , e.LastName , s.ProductID, p.Name 
HAVING sum ( s.Quantity ) < 
(select AVG(s2.Quantity) from dbo.Sales s2 where s2.ProductID=s.ProductID ) 

```

Any help is welcome, thanks in advance.

Answer: 
Alright, this is what I was looking for:

After a bit of research, I found the [Highlighting feature](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-body.html#request-body-search-highlighting) of elasticsearch. 

By default it returns a snippet of context surrounding the match, but you can set the fragment size to the query length to return only the exact match. For example:

```
{
    query : query,
    highlight : {
        "fields" : {
            'text' : {
                "fragment_size" : query.length
            }
        }
    }
}

```
Using `explain` should give you some clues, although not very easily available.

If you run the following, also available at <https://www.found.no/play/gist/daa46f0e14273198691a> , you should see e.g. `description: "weight(text:nesting^0.85714287 in 1) […]`, `description: "weight(text:testing in 1) [PerFieldSimilarity] […]` and so on in the hit's `_explanation`.

```
#!/bin/bash

export ELASTICSEARCH_ENDPOINT="http://localhost:9200"

# Create indexes

curl -XPUT "$ELASTICSEARCH_ENDPOINT/play" -d '{}'

# Index documents
curl -XPOST "$ELASTICSEARCH_ENDPOINT/_bulk?refresh=true" -d '
{"index":{"_index":"play","_type":"type"}}
{"text":"The boy was resting"}
{"index":{"_index":"play","_type":"type"}}
{"text":"The bird was testing while nesting"}
'

# Do searches

curl -XPOST "$ELASTICSEARCH_ENDPOINT/_search?pretty" -d '
{
    "query": {
        "match": {
            "text": {
                "query": "testing",
                "fuzziness": 1
            }
        }
    },
    "explain": true
}
'

```