Accelerate queries with multi-column indexes by using TairSearch -

TairSearch is a real-time in-memory retrieval module that offers both caching and computing capabilities. As the core feature of the module, inverted indexes support both storage of hot data and fuzzy match query by word root. This helps integrate data storage and computing to achieve faster queries with multi-column indexes.

Background information

Typically, critical data of application services in the Internet and traditional industries is stored in relational databases such as MySQL. To reduce database access loads and improve query efficiency, a key-value caching system like Redis is used to query hot data. However, a regular key-value caching system cannot independently work for queries with multi-column indexes or LIKE-based fuzzy match queries. In this case, additional filtering mechanisms such as Lua scripting and on-disk computing are required to implement queries with multi-column indexes.

Restrictions of a key-value caching system on queries with multi-column indexes

Typically, when data is imported from a relational database into a key-value caching system such as Redis, rows are stored in hashes. The primary key of these rows is used as the hash key. Other fields of these rows and their values are used as the hash fields and their values.

If a query involves only the primary key index, you can run the HMGET command in Redis to query the information of a specific field. Relational databases allow you to create a primary key index, multiple secondary indexes, and multi-column indexes to improve query efficiency. In comparison, Redis has limits in the following scenarios:

Queries with secondary indexes: Redis does not allow queries by hash field. Therefore, you must create a two-layer hash structure to store secondary indexes of relational databases. However, this two-layer hash structure makes it more complex to import data into Redis and causes memory waste due to data redundancy.
Queries with multi-column indexes: Redis does not allow multi-column queries on hashes. Multi-column queries can be implemented only by means of Lua scripting or on-disk computing.

This topic describes how to use TairSearch to accelerate queries with multi-column indexes. In this example, TairSearch is used to support flight tickets search during traffic surges.

Note For more information about TairSearch, see Search.

Example of accurate flight ticket search

During public holidays, traffic surges may occur as a result of huge volumes of searches for flights to popular tourist destinations. In this case, TairSearch can be used to store flight information.

Create document indexes

In this example, accurate flight ticket search involves keywords such as the place of departure, the destination, the departure date, the seat class, and whether to travel with an infant or a child.

In this example, the combination of the departure place and the destination is used as the key (departure_destination) of an index in TairSearch. Example of the key value: zhuhai_hangzhou. The following index fields are created: departure, destination, date, seat, with, flight_id, price, departure_time, and destination_time.

Note If you want to add or modify fields, run the TFT.UPDATEINDEX command.

Sample code:

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

Write data to a TairSearch document

Write flight information to a TairSearch document. Sample code:

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"}"
# The document ID is returned in the JSON format.

Accurately search for flight information

Search for flights that have first-class seats and are scheduled to depart on September 1, 2022 from Zhuhai to Hangzhou. Display the flights in order of their departure time. Sample code:

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
        }
    }
}"