User Table (tens of millions), Order Table (billions), Association Table (User ID, Order ID) - Can TiFlash columnar storage solve the issue of querying user order information without joining the order table through the association table?

Translated
1

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

Original topic: 用户表（千万级），订单表（十亿级） 关联表（用户ID、订单ID） 虽实现了数据隔离，但是每次查询用户订单信息都需要通过关联表关联订单表（订单表数据量大），通过tiflash列存方式是否解决不用通过关联表关联订单表的解决方案？

| username: xiaoxiaozuofang

【TiDB Usage Environment】Production Environment
【TiDB Version】tidb v.6.1.0
【Reproduction Path】What operations were performed when the issue occurred
【Encountered Issue: Issue Phenomenon and Impact】
User table (tens of millions), order table (tens of billions), association table (user ID, order ID). Although data isolation has been achieved, every time user order information is queried, it needs to be associated with the order table through the association table (large amount of data in the order table). Is there a solution to avoid associating the order table through the association table using the TiFlash columnar storage method?

【Resource Configuration】
【Attachments: Screenshots/Logs/Monitoring】

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

No. Whether it is related or not has nothing to do with TiFlash; it is related to your table design.
Using TiFlash is useful for operations like sum and avg on certain columns frequently.
In your scenario, can you optimize the display, for example, by only showing the order number on the list page and querying the order table for details based on the order number when viewing details? It should be fast if it uses an index.

3
| username: 裤衩儿飞上天 | Original post link

TiFlash cannot eliminate joins.
To eliminate joins, you would need to design a wide table, but wide tables come with their own issues, and you still need to adhere to most design norms.
For your type of business, using indexes should be relatively fast. If the order table is extremely large, index scanning can also be time-consuming, so you might want to consider partitioned tables.

4
| username: wfxxh | Original post link

Whether to associate or not is determined by your table design and business requirements. Are you asking if TiFlash can support your wide table format of user table + order table?

5
| username: xiaoxiaozuofang | Original post link

Given the current table design, is there a better way to solve the relationship between associated tables? After all, the order table has a large amount of data, and each query is quite time-consuming. Also, is using TiFlash and wide tables feasible based on the current data volume?

6
| username: Lucien-卢西恩 | Original post link

If it’s from the perspective of whether joins are supported, they definitely are, but performance requirements and configuration needs must be tested in practice.

7
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.