How to Reorganize a Table in TiDB

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

Original topic: tidb如何重新组织某个表

| username: 特雷西-迈克-格雷迪

[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path]
[Encountered Problem: Problem Phenomenon and Impact]

Querying a specific table is very slow: select * from tab_A limit 10 (800,000 rows)

I want to reorganize the table, but I don’t know the syntax.

[Attachment: Screenshot/Log/Monitoring]

| username: 连连看db | Original post link

ANALYZE TABLE tab_A

| username: Kongdom | Original post link

However, a limit query without a where condition shouldn’t be too slow. You can use EXPLAIN ANALYZE SELECT * FROM tab_A LIMIT 10 to get the execution plan and see where it is slow.

| username: forever | Original post link

Setting the GC time too long can also slow it down, as it will scan too many useless keys.

| username: 小龙虾爱大龙虾 | Original post link

There’s no need to reorganize the table. If it’s slow, just find the reason for the slowness.

| username: zhanggame1 | Original post link

Check the table with SHOW TABLE XXX REGIONS to see the number of regions. TiDB evaluates the data volume more appropriately by referencing regions.

| username: YuchongXU | Original post link

ANALYZE TABLE

| username: zhanggame1 | Original post link

Reorganize according to the following steps:
create new_table like old_table;
insert into new_table select * from old_table;
drop table old_table;
rename new_table to old_table;

| username: 江湖故人 | Original post link

First, check if all TiKV nodes are functioning properly.

| username: Jellybean | Original post link

Maddy, what exactly do you mean by reorganizing the table? Your goal should be to solve the slow query, right?

If it’s to solve the slow query, you can find this SQL through the Dashboard interface and then check the corresponding execution plan information. You should be able to quickly identify the cause.

| username: TIDB-Learner | Original post link

It feels like you want to reorganize the index?

| username: TiDBer_lBAxWjWQ | Original post link

Are you looking to reorganize the table to rebuild it or to analyze it?

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

You can execute explain analyze select * from tab_A limit 10 to check the execution plan.

| username: 哈喽沃德 | Original post link

What is the point of querying without conditional filtering?

| username: h5n1 | Original post link

  1. Create a new table, insert data, rename, and delete the old table.
  2. Manually compact, you can use the script written by the expert: ops/tidb-compact.py · wencycool/something_for_tidb - Gitee.com
| username: dba远航 | Original post link

alter table tab_A engine=innodb;

| username: zhanggame1 | Original post link

TiDB definitely does not support this.

| username: 随缘天空 | Original post link

Use an ETL tool to extract data into a new table.

| username: 哈喽沃德 | Original post link

Rename the table, re-extract the data, and then rename it back.

| username: 数据库真NB | Original post link

Recreate the table, make sure the indexes and primary keys are set reasonably. If it still doesn’t work, you need to check if some of your parameter settings are problematic, and whether a full table scan is occurring.