Accelerate multi-column queries using TairSearch - Tair (Redis® OSS-Compatible)

TairSearch is a real-time, all-in-memory search system that integrates caching and computation. Its core inverted index mechanism not only supports fuzzy matching queries on word roots but also allows TairSearch to serve as a hot data store. This compute-storage integration accelerates multi-column queries across any number of columns.

Background information

Many applications store key data in a relational database such as MySQL. To reduce database load and improve performance, developers often add a KV cache system like Redis to handle queries for hot data. However, when a use case requires multi-column queries or LIKE fuzzy match queries in the database, a standard KV cache system cannot handle these scenarios on its own. This requires additional filtering and matching mechanisms, such as Lua scripts or external computation, to perform multi-column queries.

Limitations of KV cache systems in multi-column query scenarios

When importing data from a relational database into a KV cache system like Redis, you typically store each row in a Redis Hash. The row's primary key becomes the Redis hash key, and the other column names and values become the hash's fields and values.

You can directly retrieve specified fields by primary key with the Redis HMGET command. However, a relational database can have not only a primary key but also multiple secondary indexes and various multi-column indexes to improve query efficiency. Redis has significant limitations in the following scenarios:

Secondary index query: Redis does not support querying a hash by its field values. Therefore, you must build a separate index in Redis to map the secondary index values back to the primary keys. This solution increases the complexity of data import and wastes memory due to data redundancy.
Multi-column query: Redis does not support queries on a hash based on multiple field values. You must implement such queries yourself, for example, with Lua scripts or external application logic.

This topic uses a flight ticket search service during a traffic surge as an example to demonstrate how TairSearch accelerates multi-column queries.

Note

For more information about TairSearch and command examples, see Search.

Precise flight search

Flight searches for popular tourist destinations can cause traffic surges during long holidays such as the summer vacation, National Day, and the Spring Festival. To handle these surges, you can store all upcoming flight information in TairSearch.

Step 1: Create a document index

On a typical flight booking platform, a precise flight search involves several key criteria: departure, destination, travel date, seat class, and whether the passenger is traveling with children or infants.

In this example, the TairSearch index name (departure_destination) is a concatenation of the departure and destination locations, for example, zhuhai_hangzhou. The following index fields are created: departure (departure), destination (destination), date (date), seat (seat), whether traveling with children or infants (with), flight ID (flight_id), price (price), departure time (departure_time), and arrival time (destination_time).

Note

To add or modify fields, run the TFT.UPDATEINDEX command.

Sample command:

TFT.CREATEINDEX zhuhai_hangzhou '{
    "mappings":{
        "properties":{
            "departure":{"type":"keyword"},
            "destination":{"type":"keyword"},
            "date":{"type":"keyword"},
            "seat":{"type":"keyword"},
            "with":{"type":"keyword"},
            "flight_id":{"type":"keyword"},
            "price":{"type":"double"},
            "departure_time":{"type":"long"},
            "destination_time":{"type":"long"}
        }
    }
}'

Expected output:

OK

Step 2: Write data

Write the flight information as a document to the TairSearch index. The following is an example command:

TFT.ADDDOC zhuhai_hangzhou '{
    "departure":"zhuhai",
    "destination":"hangzhou",
    "date":"2022-09-01",
    "seat":"first",
    "with":"baby",
    "flight_id":"CZ1000",
    "price":986.1,
    "departure_time":1661991010,
    "destination_time":1661998210
}'

Expected output:

"{"_id":"16615908912020060"}"
# Returns the ID of the new document.

Step 3: Precise search

To search for first-class flights from Zhuhai to Hangzhou on September 1, 2022, sorted by departure time, run the following command:

TFT.Search zhuhai_hangzhou '{"sort":["departure_time"],"query":{"bool":{"must":[{"term":{"date":"2022-09-01"}},{"term":{"seat":"first"}}]}}}'

Expected output:

"{
    "hits": {
        "hits": [
            {
                "_id": "16615908912020060",
                "_index": "zhuhai_hangzhou",
                "_score": 0.433955,
                "_source": {
                    "departure": "zhuhai",
                    "destination": "hangzhou",
                    "date": "2022-09-01",
                    "seat": "first",
                    "with": "baby",
                    "flight_id": "CZ1000",
                    "price": 986.1,
                    "departure_time": 1661991010,
                    "destination_time": 1661998210
                }
            }
        ],
        "max_score": 0.433955,
        "total": {
            "relation": "eq",
            "value": 1
        }
    }
}"