Databases and tables

How do I properly create a table?

AnalyticDB for MySQL stores, indexes, and queries data by partition in a distributed architecture. Three design decisions have the most impact on performance: distribution fields, partition fields, and whether to use replicated tables.

Distribution fields

Distribution fields determine how rows are spread across backend nodes. Use appropriate distribution fields to evenly distribute data across all backend nodes and ensure high resource utilization.

• If data concentrates on a small number of nodes (hot spots), write performance degrades. Re-evaluate your distribution field if you observe uneven node utilization.

Partition fields

AnalyticDB for MySQL creates and queries indexes at the partition level. Two failure modes to avoid:

• Too many partitions with few rows each: Queries scan a large number of partitions, degrading scan performance.

• Too few partitions with many rows each: Index creation within a partition runs frequently, affecting stability.

Replicated tables

A replicated table is stored on every node. This makes cross-node JOINs fast—no network transmission is needed—and improves concurrent processing throughput. The trade-off: every INSERT, UPDATE, and DELETE must be applied to all copies. Avoid creating large replicated tables or running frequent DML operations on them.

What is the maximum number of tables per cluster?

See Limits for the current quota.

Why can't I see partition information after creating a table?

Partition information is not populated automatically. After creating a partitioned table, run BUILD TABLE to make partition data queryable. See BUILD for syntax details.

How do I change the lifecycle of a partitioned table?

Run the following statement:

ALTER TABLE db_name.table_name partitions N;

Can I change distribution fields or partition fields?

No. AnalyticDB for MySQL does not support changing distribution fields or partition fields in place.

To switch to different fields, migrate the data manually:

1. Create a temporary table with the current schema.

2. Copy data into it: INSERT INTO temp_table SELECT * FROM original_table;

3. Drop the original table.

4. Create a new table with the target distribution fields or partition fields.

5. Copy data from the temporary table: INSERT INTO new_table SELECT * FROM temp_table;

How do I query partition statistics?

SELECT partition_id,      -- Partition name
       row_count,         -- Total rows in the partition
       local_data_size,   -- Local data storage size
       index_size,        -- Index size
       pk_size,           -- Primary key index size
       remote_data_size   -- Remote data storage size
FROM information_schema.kepler_partitions
WHERE schema_name = '$DB'
  AND table_name = '$TABLE'
  AND partition_id > 0;

Replace $DB with your database name and $TABLE with your table name.

What determines the number of shards? Can I adjust it?

The number of shards is calculated automatically from the cluster specifications at creation time and cannot be adjusted after the cluster is created.

How do I drop an index?

ALTER TABLE db_name.table_name DROP KEY index_name;

For parameter details and examples, see ALTER TABLE.

Why do I get a "schema is not empty" error when dropping a database?

If you delete a database that contains tables and data, the "schema is not empty" error is reported. You must delete all tables from the database first before dropping it.

Use Data Management (DMS) instead of running DROP TABLE manually—it is faster when you have many tables:

1. Log on to the DMS 5.0 console.

2. Click the connected instance name, then right-click the target database and select Single-database Query.

3. Right-click any table name and select Batch operation table.

4. Select all tables, click Delete Table, and confirm by clicking OK.