All Products
Search

This Product

    AnalyticDB:General SQL optimization methods

    Document Center

    AnalyticDB:General SQL optimization methods

    Last Updated:May 17, 2024

    You can optimize SQL queries to improve query performance and simplify database operations. For example, you can add indexes, select appropriate data types, exclude SELECT * statements, and use temporary tables to store intermediate results.

    Optimization capabilities

    • Limit the number of queries that contain IN or OR conditions. A large number of these queries may slow down root cause analysis (RCA) optimization.

    • We recommend that you do not use complex expressions or functions in WHERE conditions. These complex expressions or functions may cause the optimizer to estimate inaccurate numbers of rows.

    Indexes

    • To avoid full table scans, you can add indexes to the columns referenced in WHERE conditions.

    • We recommend that you do not add the != or <> operator to WHERE conditions. Indexes can be used only when =, <, <=, >, >=, or BETWEEN is added to WHERE conditions.

    • We recommend that you do not add OR conditions to WHERE clauses. A large number of OR conditions may make indexes less applicable. If an SQL statement does not use an index in a column as expected, you can check whether a large number of OR conditions exist and use a UNION clause to optimize the SQL statement.

    • We recommend that you replace LIKE conditions with full-text search. B-tree indexes support only the leftmost LIKE condition that does not contain wildcards. For other LIKE conditions, you must use GiST or GIN indexes supported by the pg_trgm extension.

    Data types

    • To improve query and connection performance, we recommend that you use numeric types instead of character string types.

    • To save storage space, reduce computing resources, and improve string comparison efficiency, we recommend that you replace CHAR(n) with VARCHAR(n).

    Data columns

    • We recommend that you do not use SELECT * statements, but specify the columns that you need based on your business requirements.

    Temporary tables

    • We recommend that you use temporary tables to store intermediate results of complex queries. These temporary tables facilitate service debugging and prevent repeated calculations.

    WHERE conditions

    • To reduce the number of comparisons, we recommend that you place frequently occurring values in front of IN clauses.

    JOIN

    • We recommend that you do not join more than 12 tables in a query. If more than 12 tables are joined in a query, we recommend that you use temporary tables to split SQL statements.

    Stored procedures or functions

    • If SQL statements can be used, we recommend that you do not use loop operations in stored procedures or functions.