2017 pset8 mashup: Slow search - how to improve search performance?

Question

I have successfully implemented search in application.py. Though ss I was testing through the app, I noticed that every search was really slow.

It's turns out that every search request was from 63KB up to 1.1MB, where in staff's implementation the largest request is 817 B (according to Network Tab in Chrome Tab).

My code:

@app.route("/search")
def search():
    """Search for places that match query."""

    # Raise exception if no args is passed
    if not request.args.get('q'): 
        raise RuntimeError("missing query")

    # Save query and concatenate SQL wildcard character "%""
    q = request.args.get('q') + "%"

    results = db.execute("SELECT * FROM places WHERE \
                        (postal_code LIKE :q \
                        OR place_name LIKE :q \
                        OR admin_name1 LIKE :q \
                        OR admin_code1 LIKE :q)", q=q)

    return jsonify(results)

Then I reduced my search to only two out of four database columns, postal_code and place_name and the requests' size reduced: 572 KB when I start a query and smaller for every new letter in query up to 991 B. But still far from the staff's solution.

1. How do the numbers of columns I search to affects the request size?
2. Why does my query creates smaller request size as I write (big for first letter, smaller and smaller for the next ones)?
3. How may I reach the performance of staff's solution. Is it possible that full-text searches may do the trick?

Answer 1

1: The more columns you are searching the more results you return. For example as you start to type the word "Boston" the search query takes 'B' and looks for every word in the column with a 'B' in it. It will return a lot of results. Now the more columns you search the more result you will get.

2: This question is linked to the first as in the more letters that are being searched the less likelihood of getting a match. For example "Bos" is going to get less matches than just "B"

3: If you implement full text searches (FTS3 and FTS4) you would then use the MATCH keyword instead of the LIKE keyword. This can be done but it is not required in this pset