Usage Rules

HTAP instances deliver good query performance. A single query uses more than half of the vCPUs. It is recommended that the QPS be less than 100.

To achieve good performance, you can use the following methods to optimize queries:

1. Simplify SQL statements by reducing invalid calculations, deleting unused fields, and avoiding SELECT.
2. Instead of querying all columns, delete those that are unnecessary.
3. Use the original data types to define field types (for example, do not define the TIME type as String). NULLABLE cannot be used to specify non-empty fields.
4. Manage data by partition. Partitions must be differentiated and are often used for queries. Only synchronize RANGE partitions created in GaussDB(for MySQL) to the HTAP instances. Do not synchronize other partitions. You can re-specify a partition key when creating a synchronization task. Too many partitions may affect performance.
5. Sort data based on the query frequency and remove unnecessary sort keys. Sort keys are critical to high performance, so you are advised to use up to 5 sort keys.
6. Add a hop index when the query condition is not in the sort key.
7. Instead of associating multiple tables to ensure data redundancy, use large wide tables. Use a small table as the right table when joining tables. You need to replace JOIN with IN.
8. Create data dictionary tables. If a table is frequently used and contains less than 500,000 records, you can use it as a data dictionary table. The dictionary tables are loaded to memory, reducing the number of JOIN operations and improving query performance.
9. Create materialized views for frequently used time-consuming queries to improve performance. It ensures that only the base table can be inserted and updated when creating a materialized view through multi-table association. The right table cannot be updated.