Issues Related to Index Creation

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 关于建立索引问题

| username: TiDBer_Terry261

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Problem Phenomenon and Impact]


Currently facing a difficulty with a very large table containing approximately 10 billion records. The query conditions are numerous, each condition can be specified individually or combined. For example, the “待我签” on the left side of the screenshot is a combination of three fields, and combined with several query fields on the right side, many indexes need to be created. I would like to ask how everyone deals with such situations. It seems that relational databases are not good at solving such problems.

[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

| username: tidb菜鸟一只 | Original post link

Would using TiFlash be better in this case, since TiFlash essentially creates an index on all fields?

| username: Anna | Original post link

TiDB does not optimize SQL; it decides whether to execute in TiFlash or TiKV based on different SQL statements.

| username: zhanggame1 | Original post link

Relational databases struggle with searching through tens of billions of records, considering using an OLAP product.

| username: redgame | Original post link

If you have to use this, try TiFlash.

| username: 我是咖啡哥 | Original post link

Commonly used fields, combined with several composite indexes on time columns. For queries based on contract number and ID number, it can be done as an equality query without much issue. However, if the title is used for a fuzzy query, it might be a bit difficult.