Can a single table with more than 1.5 billion rows in TiDB be converted into a partitioned table?

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

Original topic: tidb单表数据量超15亿,可以将该表修改成分区表吗?

| username: TiDBer_RQobNXGv

Can a single table with more than 1.5 billion rows in TiDB be modified into a partitioned table by time? Will this improve query performance?

| username: h5n1 | Original post link

It does not support directly converting to a partitioned table online; a new table needs to be created. Whether query performance can be improved depends on the specific SQL situation. For example, if partitioning allows scanning all data in a specific partition instead of the entire table, performance can be improved.

| username: TiDBer_RQobNXGv | Original post link

Currently, there are no TiFlash nodes in the cluster. If a TiFlash node is added, will it improve the query performance of that large table?

| username: h5n1 | Original post link

It all depends on the specific issue and requires specific analysis.

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

If it’s a statistical type of SQL, TiFlash will improve efficiency, but having only one node is a bit insufficient…

| username: Kongdom | Original post link

I remember there were some issues with index usage during cross-partition queries. You might want to verify that again. However, since you’re using TiDB, I recommend horizontal scaling to distribute the query load. If it’s an analytical query, I suggest enabling TiFlash.

| username: 考试没答案 | Original post link

If partition pruning is used with partitioned tables, it will definitely improve performance. If partition pruning is not used, for distributed systems: multiple servers working together can provide query performance. For analytical and aggregate queries, TiFlash’s MPP can improve performance, but multiple TiFlash instances are required.

| username: 考试没答案 | Original post link

You’d better post the slow SQL and table structure for us to take a look. Sometimes partitioning is not necessary.

| username: WalterWj | Original post link

You need to export and import, and modify the table structure.